create & update trigger
-
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
-
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 itemscreate trigger TR_TestStock_INSERT on dbo.mal_test After insert asbegindeclare @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 INSERTEDEXEC msdb.dbo.sp_send_dbmail@recipients = '#email@address.com',@subject = 'Inventory Delete/Create',@body = @msgendGO--deleted itemsCREATE trigger TR_TestStock_DELETE on dbo.mal_test After DELETE asbeginSET NOCOUNT ONdeclare @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 DELETEDEXEC msdb.dbo.sp_send_dbmail@recipients = '#email@address.com',@subject = 'Inventory Delete/Create',@body = @msgendLowell
--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! -
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?
-
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 exampleselect TOP 6@msg = @msg + 'New Item created with a Product Code "' + rtrim(StockCode) +'" and a description "' + rtrim(Description) +'". ' + CHAR(13) + CHAR(10)FROM mal_testPRINT @msgLowell
--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! -
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 ?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply