May 28, 2008 at 4:33 pm
Hi,
I need a little help to understand why my trigger is not working correctly. Basically my trigger should insert 1 row into 1 table and another 2 rows into a second table. (2 INSERT statements)
When testing, I can see 1 row in TableTarget1 and 1 row in TableTarget2 (from the first INSERT). I do not catch any errors ...
I have the following logic in my trigger:
TRIGGER SourceTable_Insert
ON SourceTable
AFTER INSERT
AS
BEGIN
BEGIN TRY
INSERT INTO TableTarget1 Select(v1, v2, v3) FROM inserted
INSERT INTO TableTarget2 Select(v1, v5, v6) FROM inserted
INSERT INTO TableTarget2 Select(v1, v5, v7) FROM inserted
END TRY
BEGIN CATCH
-- send email to admin
exec msdb.dbo.sp_send_DBMail ...
END CATCH
END
GO
May 28, 2008 at 5:26 pm
I'm not going to address the WHY you are doing this or suggest any better ways, this is simply a suggestion as i'm not sure why your trigger doesn't work as is. The first solution requires you to create a TALLY table explained in various posts on this site.
This is just something to try .. 😀
TRIGGER SourceTable_Insert
ON SourceTable
AFTER INSERT
AS
BEGIN
BEGIN TRY
INSERT INTO TableTarget1
SELECT v1, v2, v3
FROM inserted AS i
INSERT INTO TableTarget2
SELECT v1, v5, CASE WHEN n = 1 THEN v6 ELSE v7 END
FROM inserted AS i
CROSS JOIN dbo.tally
WHERE n IN (1,2)
END TRY
BEGIN CATCH
-- send email to admin
EXEC msdb.dbo.sp_send_DBMail ...
END CATCH
END
GO
OR this might work as well ..
TRIGGER SourceTable_Insert
ON SourceTable
AFTER INSERT
AS
BEGIN
BEGIN TRY
INSERT INTO TableTarget1
SELECT v1, v2, v3
FROM inserted
INSERT INTO TableTarget2
SELECT v1, v5, v6
FROM inserted
UNION ALL
SELECT v1, v5, v7
FROM inserted
END TRY
BEGIN CATCH
-- send email to admin
EXEC msdb.dbo.sp_send_DBMail ...
END CATCH
END
GO
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 29, 2008 at 7:28 am
It works ....
After seeing your post with 'it should work' and after a night of sleep I saw what was wrong: I was joining the current table with another table which was not returning anything.... so that's why I was seing just 1 line in the second target table.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply