在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
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货