本文介绍了如何在sqlserver2008中编写更新,删除触发器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! hii to all, 我有一个要求,我需要在两个表之间迁移或复制数据。实际上我有两个表Mainlog(在用户数据库中)&备用日志(在users2 db中)具有相同的结构存在于不同的数据库中,现在如果有任何数据被插入到主日志表中,它应该自动复制到备用日志表,我已经通过编写一些插入触发器来完成此操作,如下所示: CREATE TRIGGER InsertLogRecords ON mainlog FOR INSERT AS BEGIN SET NOCOUNT ON IF EXISTS ( SELECT * FROM mainlog) INSERT INTO users2.dbo.sparelog SELECT * FROM 插入 ELSE INSERT INTO users2.dbo.sparelog SELECT * FROM 已插入 END 以上触发器工作正常并且在插入任何数据时自动将数据复制到sparelog表。现在甚至我想要对主日志表执行更新和删除操作时执行相同操作相同的更新,删除操作应该自动反映到备用日志表。请指导我如何使用单个触发器或两个触发器(如更新,删除触发器)执行此操作,请尝试提前帮助我。谢谢提前解决方案 Hi Ram, CREATE TRIGGER 德莱特eLogRecords ON mainlog FOR DELETE AS BEGIN SET NOCOUNT ON IF EXISTS ( SELECT * FROM mainlog) INSERT INTO users2.dbo.sparelog SELECT * FROM 已删除 ELSE INSERT INTO users2.dbo.sparelog SELECT * FROM 已删除 结束 < pre lang = sql > 创建TRIGGER UpdateLogRecords ON mainlog FOR UPDATE AS BEGIN SET NOCOUNT ON IF EXISTS(SELECT * FROM mainlog) INSERT INTO users2.dbo.sparelog SELECT * FROM已插入 ELSE INSERT INTO users2.dbo.sparelog SELECT * FROM已插入 END < / pre > 查看以上查询。 希望它有用 问候, Babu.K hii to all, I have a requirement that,i need to migrate or copy the data between two tables.Actually i have two tables Mainlog(in user db) & Sparelog(in users2 db) with same structure present in different databases,now if any data is inserted into mainlog table it should automatically copy to sparelog table, i have done this by writing some insert trigger like below:CREATE TRIGGER InsertLogRecordsON mainlog FOR INSERTASBEGIN SET NOCOUNT ON IF EXISTS(SELECT * FROM mainlog) INSERT INTO users2.dbo.sparelog SELECT * FROM inserted ELSE INSERT INTO users2.dbo.sparelog SELECT * FROM insertedENDThe above trigger is working fine and copying the data to sparelog table automatically when ever any data is inserted.Now even i want the same action when update and delete operation is performed to mainlog table the same update,delete action should automatically reflect to sparelog table.Please guide how can i do this with single trigger or with two triggers like update,delete triggers,please try to help me with code.thanks in advance 解决方案 Hi Ram,CREATE TRIGGER DeleteLogRecordsON mainlog FOR DELETEASBEGIN SET NOCOUNT ON IF EXISTS(SELECT * FROM mainlog) INSERT INTO users2.dbo.sparelog SELECT * FROM deleted ELSE INSERT INTO users2.dbo.sparelog SELECT * FROM deletedEND<pre lang="sql">CREATE TRIGGER UpdateLogRecordsON mainlog FOR UPDATEASBEGIN SET NOCOUNT ON IF EXISTS(SELECT * FROM mainlog) INSERT INTO users2.dbo.sparelog SELECT * FROM Inserted ELSE INSERT INTO users2.dbo.sparelog SELECT * FROM InsertedEND</pre>Check the above query.Hope its usefulRegards,Babu.K 这篇关于如何在sqlserver2008中编写更新,删除触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-20 19:47