Trigger not working properly...

Question

Trigger not working properly...

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 13, 2015 at 4:51 am

#303470

Hi..
I have created one trigger in table on insert but it is catching previous row not inserted row.. what could be reason?
ALTER TRIGGER [dbo].
ON [dbo].[IMS_Suggestion_Mst]
after insert
as
DECLARE
@Name varchar(50),
@Email varchar(255),
@body varchar(max),
@Subject varchar(max),
@sid int
select
@Name=c.empname,
@Email=c.email_id,
@body =a.Sugg_No + a.Sugg_Title,
@subject =a.Sugg_Title
FROM dbo.IMS_Suggestion_Mst AS a INNER JOIN
dbo.IMS_Emp_Suggestion_Details AS b ON a.Sugg_No = b.Sugg_No INNER JOIN
hrms.dbo.empmst_h AS c ON b.Emp_No = c.EMP_NO
order by cast (a.Sugg_No as int )
exec msdb.dbo.sp_send_dbmail
@profile_name = 'db_mail', -- Get this value from Server configuration
@recipients = @Email,
@subject = @subject,
@body = @body

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

There's a number of things wrong here.

Firstly you're not using the inserted pseudotable, instead depending on some column in the table. Use inserted to get the inserted rows and new values for updated rows and deleted to get deleted rows and old values for updated rows

Second, that trigger, even if converted to use inserted, will not work correctly if multiple rows are inserted in a single statement. SQL triggers do not fire once per row, they fire once per operation

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

i posted an old trigger example that sends an email you could use as a model here:

it gathers specific elements from the 1-to-many rows of data that was changed, and sends an email.

As Gail mentioned, a trigger needs to be using the pseudo tables INSERTED and DELETED to gather the data that was modified.

http://www.sqlservercentral.com/Forums/Topic1368909-391-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rich Mechaber SSChampion Points: 11130 More actions · Answer 3

Another thing to watch out for: sending email from inside a trigger is generally considered a bad idea. Ideally, you want your triggers to execute quickly and stop holding up transaction completion. Waiting for a remote process like sending mail can result in unacceptable delays. Think how long that trigger will run if you inserted 1,000 rows into the table. Or 10,000.

A common way around this problem is to insert the values for @Email (recipients), @body, @subject, etc. into a table, with a DATETIME column called, say, EmailProcessed, with a default value of NULL. Then you can create an Agent job to query for all NULL rows, send out the emails, and update the table with the current datetime.

In other words, send the emails asynchronously.

This also gives you the added benefit of having a log table of email delivery, which may help you in other ways. If you are sure you don't need that confirmation persisted, you can simply delete the row after sending and dispense with the DATETIME.

Rich