March 29, 2010 at 7:23 am
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
March 29, 2010 at 7:57 am
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
March 29, 2010 at 8:06 am
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? 😉
March 29, 2010 at 8:27 am
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
March 29, 2010 at 8:37 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply