首页 > 代码库 > SQLServer创建触发器,更新表

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

SQLServer创建触发器,更新表