| 導購 | 订阅 | 在线投稿
分享
 
 
 

SQLServer 創建觸發器,更新表

來源:互聯網  2008-06-24 08:57:04  評論

在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

在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- 王朝網路 版權所有