关于MSSQL触发器 [英] About MSSQL Triggers

问题描述

我读了很多文章说避免在触发器中使用光标





我有一张桌子[测试]：

ID int，

名称nvarchar（20），

状态nvarchar（20），

... 。



当删除发生时，如果Status！='normal'，删除记录的一些数据应该记录到另一个表中。



这只是一个简单的例子，可能不实用，也许代码无法更改（对于错误修复，只能更改数据库代码），

我可以写这样的东西：

  CREATE  TRIIGER trig_DoSomething 
  ON  [Test] AFTER  DELETE  
  AS  
  BEGIN  
  DECLARE   @cur   CURSOR  
  DECLARE   @ status   nvarchar  （ 20 ）
   -   其他声明 
  SET  @ cur = CURSOR  FOR   SELECT 状态，...  FROM 已删除
  OPEN   @ cur  
  FETCH  NEXT  FROM   @ cur   INTO   @ status ，... 
  WHILE  @@ FETCH_STATUS = 0 
  BEGIN  
  IF  @status！= ' 正常' 
  BEGIN  
   -   将数据记录到另一个表中 
  END  
  FETCH  NEXT  FROM   @ cur   INTO   @ status ，... 
  END  
  CLOSE   @ cur  
  DEALLOCATE   @ cur  
  END  

更好的方法吗？

解决方案

试试这个：

  CREATE  TRIIGER trig_DoSomething  ON  [Test] AFTER  DELETE  
  AS  
  BEGIN  
 插入 进入 table1（column1，column2，column3）
 选择 column1，column2，column3 来自已插入其中状态！ = '  normal'; 
  END  

避免使用 CURSOR ，你试图杀死你的系统性能。

你好，

试试这个：

  CREATE  TRIIGER trig_DoSomething  ON  [测试] AFTER  DELETE  
  AS  
  BEGIN  
 声明  @ Col1   INT ; 
 ..   -   表格中的其他列 
 声明  @ status   varchar （ 50 ）; 
 
 选择  @ Col1  = d.Col1 来自已删除; 
 ..   -   表格中的其他列 
 选择  @ status  = d.status 来自已删除d; 
 
  if （ @ status ！= '  normal'）
  begin  
  delete  来自 table1 其中 col1 =  @ Col1中; 
 
 插入 进入 table2（Col1，Status，..）值（ @ Col1 ， @ Status ，..） ; 
  end  
  else  
 开始 
 删除 来自 表  1  其中 Col1 =  @ Col1 ; 
   -   只需删除记录即可。无需添加另一个表（根据您的逻辑）。 
  end  
  END  

I read a lot of articles that say "avoid using cursors in triggers"


I have a table [Test]:
ID int,
Name nvarchar(20),
Status nvarchar(20),
....

when delete happends, if Status!='normal', some data of the deleted records should be logged into another table.

well it's just a simple example, maybe not practical, maybe the code cannot be changed(for a bug fix, which only database code can be changed),
I may write something like this:

CREATE TRIIGER trig_DoSomething
ON [Test] AFTER DELETE
AS
BEGIN
   DECLARE @cur CURSOR
   DECLARE @status nvarchar(20)
   --other declares
   SET @cur=CURSOR FOR SELECT Status,...FROM deleted
   OPEN @cur
   FETCH NEXT FROM @cur INTO @status,...
   WHILE @@FETCH_STATUS=0
   BEGIN
      IF @status!='normal'
      BEGIN
         --log the data into another table
      END
      FETCH NEXT FROM @cur INTO @status,...
   END
   CLOSE @cur
   DEALLOCATE @cur
END

any better way?

解决方案

Try this:

CREATE TRIIGER trig_DoSomething ON [Test] AFTER DELETE
AS
BEGIN
insert into table1 (column1,column2,column3)
select column1,column2,column3 from Inserted where Status!='normal';
END

Avoid using CURSOR ,you are trying to kill your System Performance .

Hi ,
Try this :

CREATE TRIIGER trig_DoSomething ON [Test] AFTER DELETE
AS
BEGIN 
declare @Col1 int;
..    -- other columns in table
declare @status varchar (50);
 
select @Col1 = d.Col1 from deleted d;
..    -- other columns in table
select @status = d.status from deleted d;

if(@status != 'normal')
begin
  delete from table1 where col1 = @Col1;
 
  insert into table2(Col1,Status,..) values (@Col1,@Status,..);
end
else
begin
  delete from table 1 where Col1 = @Col1;
--Simply delete the record. No need to add in another table (as per your logic).
end
END

这篇关于关于MSSQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！