SQLServer 創建觸發器，更新表

在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

• ·Linux系統下統計目錄及其子目錄文件個數
• ·Linux下文件的atime, mtime和c
• ·基于Java線程實現後台定時監控
• ·在java 中執行觸發器代碼、創表語句
• ·JDBC 連接MySQL 問題
• ·觸發器中的inserted表和deleted表
• ·JDBC學習筆記(1)
• ·精彩時尚，充滿夏日陽光的家[組圖]
• ·多元風格盡現家居空間的舒適
• ·個性壁鍾記錄家居的點滴生活[組圖]