烦人的sybase

来源：互联网发布：智能排班软件编辑：程序博客网时间：2024/05/20 07:50

1：概述

听说快要被淘汰了，网上的资料也很少。

2：注意点

大小写非常敏感，sql语法和sqlserver差不多

3：sql举例，备忘

--创建视图 v_cdr_madz

create view FFas  select DA.ID as patientid,               DA.MZHM as clinicid,               DA.SFZH as idcard,               case when DA.SFZH is null then '' else '01' end as idcardcode,               '-' as healthcardid,               '-' as healthinsurancecardid,               DA.BRXM as name,               DA.CSNY as birthdatetime,               case when (DA.BRXB = 1 or DA.BRXB = 2) then DA.BRXB else 9 end as sex,               case when (DA.HYZK < 1 or DA.HYZK > 4 or DA.HYZK is null) then 9 else DA.HYZK end as maritalstatus    from MS_BRDA as DA

创建触发器

CREATE  TRIGGER TR_CDR_MS_BRDA  ON MS_BRDA  FOR INSERT,UPDATE,DELETEASBEGIN     DECLARE @V_SOURCEID NUMERIC(30)     DECLARE @C_LOGTYPE  CHAR(1)      IF EXISTS(SELECT 1 FROM inserted)          BEGIN               SELECT @V_SOURCEID = ID FROM inserted               IF EXISTS(SELECT 1 FROM deleted)                                  SET @C_LOGTYPE='3'               ELSE                                       SET @C_LOGTYPE='1'          END     ELSE                   BEGIN                 SELECT @V_SOURCEID = ID FROM deleted               SET @C_LOGTYPE='2'          END        IF (@V_SOURCEID IS NOT NULL)     INSERT INTO LOG_OV_PATIENTBASICINFO (LOGTYPE,LOGTIME,SOURCETABLE,SOURCEID,MASTERTABLEFLAG)     VALUES (@C_LOGTYPE,GETDATE(),'MS_BRDA',@V_SOURCEID,'1')END

注意：不能有分号
1：表是否存在

select  count(*)  from sysobjects where name='{0}' and type='U'

2：触发器是否存在

select count(*)  from sysobjects where name='{0}' and type='TR'

3：视图是否存在

select count(*) from sysobjects where name = '{0}'  and type = 'V'

4：得到触发器的创建脚本

select m.text from sysobjects o,syscomments m where  o.type='TR' and o.name='{0}' and o.id=m.id

5：得到视图的创建脚本

select m.text from sysobjects o,syscomments m where  o.type='V' and o.name='{0}' and o.id=m.id"

0 0