Sql触发器

附一：sql脚本

---------------对重复住院号问题处理
ALTER TABLE LFMIS..JCMXXM ADD ZYCS INT NOT NULL DEFAULT '0'
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'ZYCH' AND type = 'TR')
   DROP TRIGGER ZYCH
GO
CREATE TRIGGER ZYCH
ON LFZYSJH
INSTEAD OF INSERT
AS
IF EXISTS (SELECT a.SJH FROM LFMIS..JC_MXXM a WHERE right(a.sjh,8) in (select ZYH from inserted) AND MZ_ZY=1)
   begin
   update lfmis..jc_mxxm
   set sjh=sjh+'x',ZYCS=1
   where right(sjh,8) in (select ZYH from inserted)

   insert into LFZYSJH(ZYH,XM,FYM,MC,SL,YMD,JE)
   select ZYH,XM,FYM,MC,SL,YMD,JE from inserted
   end
else
   insert into LFZYSJH(ZYH,XM,FYM,MC,SL,YMD,JE)
   select ZYH,XM,FYM,MC,SL,YMD,JE from inserted
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------接口与控制器信息同步
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
use LFMIS
go
if exists(select name from sysobjects where name='sfbm_TJ' and type='tr')
drop TRIGGER sfbm_TJ
go
create TRIGGER sfbm_TJ
ON SYS_SFXM
FOR delete, INSERT, UPDATE
AS
DECLARE @LF_COU NUMERIC(5),
@XX_COU NUMERIC(5)
SELECT @LF_COU=COUNT(*) FROM SYS_SFXM
SELECT @XX_COU=COUNT(*) FROM SFXX.DBO.SFBM
--SELECT @LF_COU,@XX_COU
IF @LF_COU>@XX_COU
    BEGIN
         DECLARE @XMBM1 VARCHAR(100),
         @XMMC1 VARCHAR(100)
         SELECT @XMBM1=A.XMBM,@XMMC1=A.XMMC FROM SYS_SFXM A WHERE NOT EXISTS (SELECT * FROM SFXX.DBO.SFBM WHERE XMBM=A.XMBM)
-----SELECT @XMBM,@XMMC
         INSERT INTO SFXX.DBO.SFBM
         SELECT @XMBM1,@XMMC1
    END
ELSE
   IF @LF_COU<@XX_COU
        BEGIN
        DECLARE @XMBM2 VARCHAR(100),
        @XMMC2 VARCHAR(100)
        SELECT @XMBM2=A.XMBM,@XMMC2=A.XMMC FROM SFXX.DBO.SFBM A WHERE NOT EXISTS (SELECT * FROM SYS_SFXM WHERE XMBM=A.XMBM)
       
        DELETE SFXX.DBO.SFBM
        WHERE XMBM=@XMBM2
        END
GO
-----------门诊信息提取接口
CREATE  PROCEDURE MZSF
AS
INSERT INTO MZXX
SELECT LSH,Name,SFDM,Brief,sl,TOLLDATE,JE
FROM TANYAN.hisgzk.DBO.sf a
WHERE TOLLDATE>DATEDIFF(day,+3,GETDATE())
AND EXISTS (SELECT * FROM SFBM WHERE XMBM=a.SFDM )
AND NOT EXISTS (SELECT * FROM MZXX  WHERE LSH=a.LSH AND TOLLDATE=a.TOLLDATE) ORDER BY TOLLDATE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO