SQLServer 创建触发器,更新表

王朝mssql·作者佚名  2008-06-24
窄屏简体版  字體: |||超大  

在SQLServer,触发器,插入、更新、删除状态: CREATE TRIGGER t_inms_alarms

ON [PHS].[dbo].[AlarmCurrent]

FOR INSERT, DELETE

AS

DECLARE @rows int

SELECT @rows = @@rowcount

IF @rows = 0

return

--如果表是插入,则同步更新AlarmsMiddleTbl

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)

BEGIN

INSERT INTO [PHS].[dbo].[AlarmsMiddleTbl]

SELECT i.[Id], i.[SequenceId], i.[code], i.[alarmdefineid],

CONVERT(varchar,i.[occurTime],120), i.[confirmation], i.[ConfirmationTime],

i.[MaintenanceName], i.[MaintenanceProcedure],

i.[ClearTime], i.[screen],CONVERT(varchar, getdate(), 120)

FROM inserted i

END

--如果表是更新某个字段,则同步更新AlarmsMiddleTbl

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) AND

UPDATE(ConfirmationTime)

BEGIN

INSERT INTO [PHS].[dbo].[AlarmsMiddleTbl]

SELECT d.[Id], d.[SequenceId], d.[code], d.[alarmdefineid],

CONVERT(varchar,d.[occurTime],120), d.[confirmation], d.[ConfirmationTime],

d.[MaintenanceName], d.[MaintenanceProcedure],

CONVERT(varchar, getdate(),120), d.[screen],CONVERT(varchar, getdate(), 120)

from deleted d

END

--如果表是删除,则同步更新AlarmsMiddleTbl

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)

BEGIN

INSERT INTO [PHS].[dbo].[AlarmsMiddleTbl]

SELECT d.[Id], d.[SequenceId], d.[code], d.[alarmdefineid],

CONVERT(varchar,d.[occurTime],120), d.[confirmation], d.[ConfirmationTime],

d.[MaintenanceName], d.[MaintenanceProcedure],

CONVERT(varchar, getdate(),120), d.[screen],CONVERT(varchar, getdate(), 120)

from deleted d

END

IF @@error <> 0

BEGIN

RAISERROR('ERROR',16,1)

rollback transaction

return

END

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航