在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创建触发器,更新表
转载上一篇:sqlserver触发器语法
下一篇:给数据库连接添加互斥锁
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
SQL server触发器在哪里 sql server 触发器
触发器(Trigger)是一种特殊类型的存储过程,是在用户对某一种表的数据进行UPDATE、INSERT 和 DELETE 操作时被触发执行的一段程序。触发器有助于强制引用完整性,以便在添加、更新或删除表中的行时保留表之间已定义的关系。
SQL server触发器在哪里 Database 触发器 数据 存储过程