1:概述
听说快要被淘汰了,网上的资料也很少。
2:注意点
大小写非常敏感,sql语法和sqlserver差不多
3:sql举例,备忘
--创建视图 v_cdr_madz
1:表是否存在
create view FF
as
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,DELETE
AS
BEGIN
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"