October 27, 2010 at 6:55 pm
Maybe this is really simple, and all I need is another set of eyes to take a look. but I haven't been able to figure it for the life of me.
I'm trying to have a simple trigger perform an insert in another table when AFTER INSERT is performed. But seems like the trigger never gets triggered and there is no data in the table. Please tell me what I could be doing wrong here. Basically when a record is inserted in test1, I want to log test2 with bunch of log data.
CREATE TRIGGER dbo.trg_Create_Instruct1
ON dbo.TEST1
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.test2(test1, Notes)
VALUES('blah blah', 'this is just test notes')
END
GO
October 27, 2010 at 7:32 pm
That looks right to me... I assume that's a simplified trigger for our benefit.
Some things can ignore triggers, like Bulk Insert has the ability to turn it off. Are you encountering that?
Is it not firing if you simply do INSERT INTO tbl1 (fld1, fld2) VALUES ('data', 'data')?
Is the trigger disabled?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2010 at 7:45 pm
Craig Farrell (10/27/2010)
That looks right to me... I assume that's a simplified trigger for our benefit.Some things can ignore triggers, like Bulk Insert has the ability to turn it off. Are you encountering that?
Is it not firing if you simply do INSERT INTO tbl1 (fld1, fld2) VALUES ('data', 'data')?
Is the trigger disabled?
See that's why I needed another set of eyes or another perspective 🙂
Yes - I am bulk inserting using SSIS ole db destination task.
So, there is no way of firing a trigger off when using bulk insert?
I guess I could just use a sql task to perform this insert, but it will be dirty.
Thanks for you insight Craig.
October 27, 2010 at 7:54 pm
Check out this article:
http://msdn.microsoft.com/en-us/library/ms141237.aspx
In particular, this section:
In addition to the fast load options exposed in the OLE DB Destination Editor dialog box,you can configure the OLE DB destination to use the following bulk load options by typing the options in FastLoadOptions property in the Advanced Editor dialog box.
Which lists this in the table:
FIRE_TRIGGERS
Specifies whether triggers fire on the insert table. The option has the form FIRE_TRIGGERS. The presence of the option indicates that triggers fire.
You get to the advanced editor by rt-clicking the object, and using 'Advanced Editor'.
Hope that helps. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 28, 2010 at 5:58 am
Beautiful 🙂
I can actually see it getting fired off!
One problem though - it was only inserting the last record of the batch. So I did some googling, and I come to find out that utilizing INSTEAD OF triggers should do the trick. So that's all I needed. Make the change you suggested in the my bulk insert task properties and use INSTEAD OF triggers.
I can't thank you enough. You just saved me ton of headache and hours worth of work! Thanks again Craig!
October 28, 2010 at 10:47 am
Jay-45986 (10/28/2010)
Beautiful 🙂I can actually see it getting fired off!
One problem though - it was only inserting the last record of the batch. So I did some googling, and I come to find out that utilizing INSTEAD OF triggers should do the trick. So that's all I needed. Make the change you suggested in the my bulk insert task properties and use INSTEAD OF triggers.
I can't thank you enough. You just saved me ton of headache and hours worth of work! Thanks again Craig!
My pleasure. Had a wallbanger last night myself that someone helped me out with. Always good to return the favor elsewhere. 😀
Without seeing your trigger/ddl, I can't tell you why the AFTER trigger was misbehaving for you. At a guess it wasn't dealing with the full rowset from inserted, a common enough error.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply