Delete triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete triggers

Author

Topic

vin
Starting Member

26 Posts

Posted - 2003-04-04 : 04:07:00

Hi,

Can somebody tell me how to capture the deleted value from a table using a trigger. The idea is store the deleted rows in an audit table.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 04:10:42

The old values will be in the deleted virtual table available in the trigger.

See
www.nigelrivett.com
Triggers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vin
Starting Member

26 Posts

Posted - 2003-04-04 : 06:26:07

Thanx NR,

Also can please tell me how to find out what all triggers are there in the database and which triggers are based on what table.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-04 : 06:34:28

HTH..

USE PUBS
GO
SELECT A.name AS TableName, B.name AS TrigerName
FROM SYSOBJECTS A INNER JOIN SYSOBJECTS B
ON A.ID = B.PARENT_OBJ
WHERE B.TYPE='TR'

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

vin
Starting Member

26 Posts

Posted - 2003-04-05 : 02:06:14

Thanx sam,

Now i have written trigger for Delte operation.
I works fine when i have equality operator.

Like

Delete from emp where empno=10

But this trigger doesn't fire when i have a range deletes..
like

Delet from emp where empno>10

Is there any special considerations to be taken for that ?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-05 : 08:11:58

It will fire but there will be many entries in the deleted table rather than just one. If you are using variables in the trigger then you are probably just acting on one of the deleted rows.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vin
Starting Member

26 Posts

Posted - 2003-04-06 : 00:40:48

Thanks NR,

but how to collect all the value and then insert into my audit table ?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-06 : 05:48:11

see
www.nigelrivett.com
triggers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vin
Starting Member

26 Posts

Posted - 2003-04-07 : 02:38:01

For eg, I have a table emp with columns...
enum,name and sal

I have written a trigger to audit the deletions on this table
which is as follows .

Create trigger Trig_emp on dbo.emp for delete
as
SET NOCOUNT ON
begin
if ((select count(*) from deleted where [sal] is not null)=1)
begin
INSERT INTO EMP_HISTORY(SAL_DEL)
SELECT SAL FROM DELETED
END
END
GO

This trigger will fire for equality queries..like
DELETE FROM EMP WHERE ENUM=6

But doesn't fire when i say
DELETE FROM EMP WHERE ENUM>3

Can please sombody tell me the exact way of getting this done.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-07 : 02:40:32

Replace:

if ((select count(*) from deleted where [sal] is not null)=1)

With

IF @@ROWCOUNT > 0

DavidM

"SQL-3 is an abomination.."

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-07 : 02:43:07

quote:

Create trigger Trig_emp on dbo.emp for delete
as
SET NOCOUNT ON
begin
if ((select count(*) from deleted where [sal] is not null)=1)
begin
INSERT INTO EMP_HISTORY(SAL_DEL)
SELECT SAL FROM DELETED
END
END
GO

This is due to the IF condition you have specified.. for multirow deletion the COUNT value will be more than 1. try changing the if condtion to >=1

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

vin
Starting Member

26 Posts

Posted - 2003-04-07 : 04:22:28

hey thanx,
it is working...

it just inserst dumps all the values for a single coulmn and then goes to the next colum....

ie., if i delet 10 rows....then it will inset all the deleted value or salray first, then all the 10 value of address next ....

is it possible something like it take all the deleted values in rowwise..

ie., sal,address, then.... sal, address, then sal, address
instead of sal,sal,sal address,address,address.........

Becoz i have a program which reads in this fashion....

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-07 : 04:46:15

You don't need the test - if there's nothing in the table then it won't do anything.
(Unles you have a trigger on EMP_HISTORY).

Create trigger Trig_emp on dbo.emp for delete
as
SET NOCOUNT ON
INSERT INTO EMP_HISTORY(SAL_DEL)
SELECT SAL FROM DELETED
GO
To do what you are suggesting nest you will need to insert the PK and the value so that the app can read these and match the values with the correct records.
Ordering in a table is a meaningles concept in a relational database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources