如何在sqlserver2008中编写更新，删除触发器 [英] How to write a update,delete trigger in sqlserver2008

问题描述

hii to all，

我有一个要求，我需要在两个表之间迁移或复制数据。实际上我有两个表Mainlog（在用户数据库中）&备用日志（在users2 db中）具有相同的结构存在于不同的数据库中，现在如果有任何数据被插入到主日志表中，它应该自动复制到备用日志表，我已经通过编写一些插入触发器来完成此操作，如下所示：

  CREATE   TRIGGER  InsertLogRecords 
  ON  mainlog  FOR   INSERT  
  AS  
  BEGIN  
  SET   NOCOUNT   ON  
  IF   EXISTS （ SELECT  *  FROM  mainlog）
 < span class =code-keyword> 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 
 < span class =code-keyword> 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 InsertLogRecords
ON mainlog FOR INSERT
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainlog)
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM inserted
 ELSE
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM inserted
END

The 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 DeleteLogRecords
ON mainlog FOR DELETE
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainlog)
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM deleted
 ELSE
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM deleted
END

<pre lang="sql">CREATE TRIGGER UpdateLogRecords
ON mainlog FOR UPDATE
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainlog)
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM Inserted
 ELSE
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM Inserted
END</pre>

Check the above query.

Hope its useful

Regards,
Babu.K

这篇关于如何在sqlserver2008中编写更新，删除触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！