Trigger on insert help

Question

Post reply

Trigger on insert help

WiRL

SSC Veteran

Points: 227
More actions
February 26, 2013 at 10:03 am

#269130

Hello All,
I hope someone can help because I'm lost as to what the problem is...
I have created a trigger:
CREATE TRIGGER new_contact
ON contacts
FOR INSERT
AS
declare @contact_id varchar(11)
declare @body varchar(2000)
declare @staff_issued varchar(10)
SELECT @contact_id = contact_id,
@staff_issued = staff_issued
FROM contacts
SET @body = 'Customer with ID:' + @conatct_id + ' has been created by: ' + @staff_issued
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'myemail@email.com',
@subject = 'New Customer',
@body = @body
GO
and then I run:
INSERT INTO contacts
(contact_id, date_issued, staff_issued)
VALUES ('ABC12345678', GETDATE(), 'ADMIN')
The insert works, the triggers runs and emails me but it sends the top result from the table "contacts" rather than the one that has just inserted???
I am obviously missing something.
Can anyone point me in the right direction??
Many Thanks in advance

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

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

Richard Warr SSCertifiable Points: 6966 More actions · Answer 1

Try this bit in the middle:

SELECT @contact_id = contact_id,

@staff_issued = staff_issued

FROM inserted

The inserted "table" contains the record you have inserted whilst contacts won't know which record you want.

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Also beware the the inserted table can contain more than one row if a multiple row insert is done.

Far away is close at hand in the images of elsewhere.
Anon.

WiRL SSC Veteran Points: 227 More actions · Answer 3

WiRL

SSC Veteran

Points: 227

February 27, 2013 at 4:52 am

#1591496

Awesome that works.

Many Thanks

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 4

WiRL (2/27/2013)
Awesome that works.
Many Thanks

Actually, it doesn't. If you insert more than one rrow into the table that fires the trigger, the trigger will only process one row. You need to rewrite the code in a set based fashion to get all of the rows. Please see Books Online (press the {f1} key) and read up on the INSERTED and DELETED logical tables that are available when the trigger is doing its job.

It's also a pretty bad idea to call email in a trigger. If email is down, the trigger could fail which will rollback the external transaction. It's a far bettter thing to insert what you want into a staging table and have a job sweep through that tablee to send the emails.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)