After Trigger - 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)

After Trigger

Author

Topic

FabioEinaus
Starting Member

41 Posts

Posted - 2006-12-20 : 12:26:38

Hi ppl!

Is there any way to know in as After INSERT, UPDATE, DELETE, what was the action done by the user?

Thanks in advance for the replys

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-20 : 12:43:27


Action		inserted rows		       deleted rows
INSERT		> 1					  0
UPDADTE		> 1					> 1
DELETE		  0					> 1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

FabioEinaus
Starting Member

41 Posts

Posted - 2006-12-21 : 05:08:18

You say if I want to test what was the action, that will be If Insert>1 then?

Sounds wierd..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 05:13:27

What Brett means is that:

If records exists in only Inserted but not Deleted table, action = INSERT
If records exists in Inserted as well as Deleted table, action = UPDATE
If records exists in only Deleted but not Inserted table, action = DELETE

You may find below info useful

quote:
From BOL:

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:17:16

The comparisons above should be >= 1 since it is possible to insert/update/delete only one row.

IF EXISTS (SELECT * FROM Inserted) AND NOT EXISTS (SELECT * FROM Deleted)
	PRINT 'INSERT ACTION'

IF EXISTS (SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)
	PRINT 'UPDATE ACTION'

IF NOT EXISTS (SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)
	PRINT 'DELETE ACTION'

Peter Larsson
Helsingborg, Sweden

FabioEinaus
Starting Member

41 Posts

Posted - 2006-12-21 : 06:54:48

I was asking this because i'm geting a problem that I already posted here in this forum:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76581

I was thinking that if I do that insert in an After INSERT, UPDATE, DELETE trigger that the problem will be solved but it still hapend..

1 Insert right and then i got 4 insert with the same information that tells me that I've updated that row and then another 4 that tells me again that I've updated the row but with the field Horas.

I don't understand why it hapend..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 07:06:38

In the link provided, you write INSTEAD OF trigger.
In this topic you write AFTER trigger.

??????

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources