在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
