About MSSQL Triggers

Question

0.00/5 (No votes)

See more:

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:

SQL

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?

Posted 4-Jan-15 15:15pm

Brian Holsen

Updated 4-Jan-15 17:36pm

v2

Add a Solution

Comments

PIEBALDconsult 4-Jan-15 21:47pm

Avoid triggers too.
You probably have a poor design; more detail might be helpful. Use Improve question.

deepakdynamite 4-Jan-15 23:03pm

Can you post your Trigger code over here with brief description... ????

deepakdynamite 4-Jan-15 23:46pm

IF @status!='normal'
Is this code correct in your procedure ???

Are you referring to Status field of your select statement or Cursor ???

Also please write code for

--log the data into another table

Probable we can solve your problem using CTE or Subqueries...

Brian Holsen 5-Jan-15 23:22pm

IF @status!='normal'
what's wrong with it?

deepakdynamite 9-Jan-15 7:27am

@status is name of cursor... and this comparison won't work

Brian Holsen 11-Jan-15 22:17pm

@cur is the name of cursor, @status is nvarchar(20)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Answer 1 · 2015-01-04T18:14:00

Solution 1

Try this:

SQL

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 .

Posted 4-Jan-15 18:14pm

King Fisher

v2

Comments

Brian Holsen 5-Jan-15 23:29pm

I used "improve question" and actually deleted some of my descriptions
that is just a simple example up there, sometimes maybe not just as simple as logging data, maybe I need to parse some data of each deleted records to a procedure to do some complex operations.
I just want to know if there is always a way to handle all kinds of situations without using cursor.

King Fisher 5-Jan-15 23:42pm

then... What about while Loop?

Sumit Jawale · Answer 2 · 2015-01-04T20:34:00

Hi ,
Try this :

SQL

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