After Insert Trigger

Question

After Insert Trigger

Hafiz Umar

Old Hand

Points: 355
More actions
January 4, 2007 at 1:19 am

#92159

I am using AFTER INSERT trigger to send an email to the user using database mail. Everthing is OK. The question is that How can I include the data of that particular inserted row to be included in the email?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

You can get the details of the new rows from the inserted table. It will contain all the rows that were inserted by the statement that fired the trigger.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hafiz Umar Old Hand Points: 355 More actions · Answer 2

How? Any select statement? I read in books online, "SELECT * from deleted" but when I include this line in my trigger, using @query parameter of database mail, it doesn't work

MohammedU SSCoach Points: 19644 More actions · Answer 3

Insert into temp table and the use temp table in send mail...

MohammedU
Microsoft SQL Server MVP

Hafiz Umar Old Hand Points: 355 More actions · Answer 4

I have found out a way

select @customername = customername from instered

then use @customername to fomulate the mail body. It works fine.

MohammedU SSCoach Points: 19644 More actions · Answer 5

Are you sure you will be getting the right data... because you were looking to send the data from deleted table where as if you use customername .. may be you will inserted data instead of deleted...

MohammedU
Microsoft SQL Server MVP

Hafiz Umar Old Hand Points: 355 More actions · Answer 6

I am using after insert trigger, so definitely I am looking into Inserted table, not deleted table. So far, I am getting the right data. I will see if any problems occur in future.

MohammedU SSCoach Points: 19644 More actions · Answer 7

Some people try to save old data instead of new...so that I raised the flag...

If it works then good luck...

MohammedU
Microsoft SQL Server MVP

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

Be aware that your solution won't handle cases where multiple rows are inserted in a single batch. Your trigger will only get one of the inserted rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass