May 12, 2014 at 9:25 am
Can you create a SQL Trigger on an existing table to populate count values, or do you have to create an empty table, then create an Insert trigger, then import the data into the new table with the new trigger?
What is the best type of trigger to use for data being imported via the SQL Import wizard, Insert Trigger?
I assume an insert trigger will fire when you import data via the SQL Import wizard ?
The more i research SQL Triggers, the more confusing it gets. Everyone has a different opinion as to the best way to do it!
Thanks
May 12, 2014 at 9:54 am
isuckatsql (5/12/2014)
Can you create a SQL Trigger on an existing table to populate count values, or do you have to create an empty table, then create an Insert trigger, then import the data into the new table with the new trigger?
If you are saying that you have an existing table, and you want to populate an existing field with values by creating a trigger, then no. Creating the trigger will not populate this field. A trigger does not work in the manner. You will need to update this field separately.
isuckatsql (5/12/2014)
What is the best type of trigger to use for data being imported via the SQL Import wizard, Insert Trigger?
None of them. At least in my experience, a trigger does not fire when using the SQL Import wizard.
isuckatsql (5/12/2014)
I assume an insert trigger will fire when you import data via the SQL Import wizard ?
See above. Try it for yourself. Create a test table, add the trigger, and import away!
isuckatsql (5/12/2014)
The more i research SQL Triggers, the more confusing it gets. Everyone has a different opinion as to the best way to do it!Thanks
Another opinion to add to your collection = AVOID TRIGGERS IF YOU CAN. Although in this case, it sounds as if a trigger MAY be the right tool for the job.
Have you looked into computed columns?
And finally, what EXACTLY are you trying to do?
Store a running total based on a series of columns?
Store the calculation on a bunch of columns?
Something else?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 12, 2014 at 10:27 am
http://www.sqlservercentral.com/Forums/Topic1568938-3412-1.aspx
This above thread explains why i am going down the trigger route.
"I had a same problem as you and I solved the problem in SSIS using 'Data access mode' 'Table or view' and not 'Table or view - fast load'.
I think if you use the fast load option that the trigger isn't fired"
I found the above information from a thread on this forum regarding triggers and SSIS import issues.
I am reading some of the earlier trigger threads on this forum, and found this:
http://www.sqlservercentral.com/Forums/Topic453629-338-1.aspx
Jeff Moden has highlighted the importance of writing the trigger correctly, so i am now trying to figure out which type of trigger to use, and how to write it so that it gets the job done correctly.
Thanks
May 12, 2014 at 11:14 am
isuckatsql (5/12/2014)
http://www.sqlservercentral.com/Forums/Topic1568938-3412-1.aspxThis above thread explains why i am going down the trigger route.
Ok, Then what are you stuck on? This thread explains it pretty well.
isuckatsql (5/12/2014)
"I had a same problem as you and I solved the problem in SSIS using 'Data access mode' 'Table or view' and not 'Table or view - fast load'.I think if you use the fast load option that the trigger isn't fired"
I found the above information from a thread on this forum regarding triggers and SSIS import issues.
In the original post, you didn't say SSIS, you said the SQL Import wizard. As far as I can tell, you cannot change from fast load unless you save and edit the package. But you figured that out! Good!
isuckatsql (5/12/2014)
I am reading some of the earlier trigger threads on this forum, and found this:http://www.sqlservercentral.com/Forums/Topic453629-338-1.aspx
Jeff Moden has highlighted the importance of writing the trigger correctly, so i am now trying to figure out which type of trigger to use, and how to write it so that it gets the job done correctly.
It's not WHAT type of trigger, it's WHEN do you need to make the change to this data?
If the data needs to be updated when an insert occurs, then use an insert trigger.
If the data needs to be updated when an update occurs, then use an update trigger.
If the data needs to be updated when a delete occurs, then use a delete trigger.
You have a couple choices. You can use a single trigger:
CREATE TRIGGER foo ON Table FOR UPDATE, INSERT, DELETE
AS
Or you can use separate triggers:
CREATE TRIGGER i_foo ON Table FOR INSERT
AS
CREATE TRIGGER u_foo ON Table FOR UPDATE
AS
CREATE TRIGGER d_foo ON Table FOR DELETE
AS
Separate triggers allow for a bit more flexibility. If this is going to be the same "process" for all three of these operations, then use a single trigger.
Does this calculation only need to occur when one of the bulk inserts occur? If so, then a trigger is not what I would do. I would add a step to the SSIS package, and run an update script for the affected rows.
If the calculation always needs to occur on an insert, update,or delete, then a trigger may be the best way.
Making an educated guess, you may see some performance hits when doing this with a trigger, especially on larger loads.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 12, 2014 at 11:57 am
Apologies, my mistake on the SSIS part.
Data is loaded via a SQL Import wizard each day, but will ultimately be done via multiple daily XML imports.
The count data will only be updated based on imported data.
"If so, then a trigger is not what I would do. I would add a step to the SSIS package, and run an update script for the affected rows.
If the calculation always needs to occur on an insert, update,or delete, then a trigger may be the best way."
While i am bulk importing via SQL import, which is only once a day, adding a step to an SSIS package does make sense.
In the long term since the imports will be via XML and happen many times a day, you say the trigger may be the best way, so to avoid duplication of effort it would make sense to just create separate triggers for each part of the process.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply