create & update trigger

Question

Post reply

create & update trigger

dopydb

SSCertifiable

Points: 6687
More actions
March 29, 2010 at 7:23 am

#217197

i have the below trigger working fine for when a new entry is created, what i need is to detect deletes as well.
what is the best way to do this, 2 trgers - 1 for each create and delete or combine into 1 overall trigger?
i have tried combining but cant seem to distinguish between what is happening? how would i best achieve that?
create trigger TestStock on dbo.mal_test After insert as
declare @StockCode varchar(10)
declare @Description varchar(10)
set @StockCode =(select StockCode from Inserted)
set @Description =(select Description from Inserted)
begin
declare @msg varchar(500)
set @msg = 'New Item created with a Product Code"' + rtrim(@StockCode) +'"and a description"' + rtrim(@Description) +'".'
EXEC msdb.dbo.sp_send_dbmail
@recipients = '#email@address.com',
@subject = 'Inventory Delete/Create',
@body = @msg
end

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

it's up to you; you can combine the functionality in the same trigger.

I'd just make a second trigger, but it is up to you.

i noticed your trigger will not send an accurate email if more than one row was inserted; while your biz process might assume only one row at a time, a trigger should be ready for multi row inserts.

i've made a suggestion here, where a string with multiple items gets created; I'd make msg much larger than varchar(500) just in case.

--inserted items

create trigger TR_TestStock_INSERT on dbo.mal_test After insert as

begin

declare @msg varchar(8000)

SET @msg = 'The Following Items were Created:' + CHAR(13) + CHAR(10)

select

@msg = @msg + 'New Item created with a Product Code "' + rtrim(StockCode) +'" and a description "' + rtrim(Description) +'". ' + CHAR(13) + CHAR(10)

FROM INSERTED

EXEC msdb.dbo.sp_send_dbmail

@recipients = '#email@address.com',

@subject = 'Inventory Delete/Create',

@body = @msg

end

GO

--deleted items

CREATE trigger TR_TestStock_DELETE on dbo.mal_test After DELETE as

begin

SET NOCOUNT ON

declare @msg varchar(8000)

SET @msg = 'The Following Items were deleted:' + CHAR(13) + CHAR(10)

select

@msg = @msg + 'Item deleted with a Product Code "' + rtrim(StockCode) +'" and a description "' + rtrim(Description) +'". ' + CHAR(13) + CHAR(10)

FROM DELETED

EXEC msdb.dbo.sp_send_dbmail

@recipients = '#email@address.com',

@subject = 'Inventory Delete/Create',

@body = @msg

end

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!

dopydb SSCertifiable Points: 6687 More actions · Answer 2

hi Lowell

im trying to see how your trigger loops looking for multiple lines? but dont see how it does!

can you point out the obvious to me? 😉

Lowell SSC Guru Points: 323504 More actions · Answer 3

it's this part:

select

@msg = @msg + 'New Item ....FROM INSERTED

if inserted has 10 rows, @msg is going to have 10 "lines", one for each row;

basis example to use as a proof:

declare @msg varchar(8000)

SET @msg = 'The Following Items were Created:' + CHAR(13) + CHAR(10)

--get first 6 rows of data as an example

select TOP 6

@msg = @msg + 'New Item created with a Product Code "' + rtrim(StockCode) +'" and a description "' + rtrim(Description) +'". ' + CHAR(13) + CHAR(10)

FROM mal_test

PRINT @msg

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!

dopydb SSCertifiable Points: 6687 More actions · Answer 4

oh yes i see now! thanks for that

i take it small triggers like these have little impact on performance so trying to make one larger trigger willl save little ?