Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.
I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.
Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)
Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.
Yeah I understand that the trigger will only fire once per batch
So here is another stupid question - where you say select * from inserted. Is inserted a key word here or where you using that as my table name?
Because basically what I am after is (if it is possible) to have (for example) a record inserted with field_3 = 1234, and field_1 = "BLAH", then the trigger to go "okay" field_1 = BLAH and then carry on and use the field_3 value (ie 1234) in the remaining query to build other records in other tables.
If what I think is correct - the "inserted" word is actually just my table name - then the trigger will still be firing a lot of times because there is already a lot of records in the table with Field_1 = BLAH.....
I fear I might be going round in circles and not making a hell of a lot of sense.....*argh*
Sorry to keep asking a million and one questions, but so this table "inserted" which can be queried from the trigger (eg select * from updated), does this have the same structure (ie fields and field names) as the actual table which the data is going to be inserted into?
And is there any way where I can pull values from that table and use them as variables in the trigger?
the answer is YES, except there are only 2 internal table, inserted and deleted. when the number of rows in both is the same it's an update operation, if deleted table is empty then it's an insert. and again, the structure of both is identical to the structure of the table for which the trigger was created.
and yes, you can store all values into variables from inserted, or reference that table throughout the execution of the trigger.