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