Update trigger.
-
Chaps, I can't seem to find a definitive answer so maybe you can advise. Here's the scenario. Let's say I have an update trigger on a table. If I have statement like (rough syntax) update mytable set x = 0 where somecolumn > 293 and it updates, say, 300 records. Would an update trigger occur on every row updated or just once for the batch of updates, as it were? A long time back something I read/misread gave me the impression that some sql statements call the trigger for each row affected and another only does it once but I'm not sure what's fact and what's fiction.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
-
Chaps, I can't seem to find a definitive answer so maybe you can advise. Here's the scenario. Let's say I have an update trigger on a table. If I have statement like (rough syntax) update mytable set x = 0 where somecolumn > 293 and it updates, say, 300 records. Would an update trigger occur on every row updated or just once for the batch of updates, as it were? A long time back something I read/misread gave me the impression that some sql statements call the trigger for each row affected and another only does it once but I'm not sure what's fact and what's fiction.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
The trigger will file once for each statement. You should always write your triggers with the assumption that the
insertedanddeletedvirtual tables will contain multiple rows.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
The trigger will file once for each statement. You should always write your triggers with the assumption that the
insertedanddeletedvirtual tables will contain multiple rows.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard, thanks for that. I need to schedule some time to look into them. I know they're basically simple concepts but it's in the doing that you understand them.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
-
Richard, thanks for that. I need to schedule some time to look into them. I know they're basically simple concepts but it's in the doing that you understand them.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
Triggers spit are a support nightmare, especially for a new developer. If you break a trigger (changing/removing a field) it can be extremely difficult for support to identify the problem (it can be bloody difficult for the silly bastard who wrote the trigger). We do use the horrible things but only for audit purposes NEVER to implement business logic, that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
Never underestimate the power of human stupidity RAH
-
Triggers spit are a support nightmare, especially for a new developer. If you break a trigger (changing/removing a field) it can be extremely difficult for support to identify the problem (it can be bloody difficult for the silly bastard who wrote the trigger). We do use the horrible things but only for audit purposes NEVER to implement business logic, that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
I wish our database at work was so obliging and obedient. :( In a previous job I remember problems with a trigger when looking for differences in the before and after images. I never worked on a more convuluted POS in my life. It was the kind of trigger that had an unwritten rota as to which mug of the month got as a prize.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
-
Mycroft Holmes wrote:
that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
I wish our database at work was so obliging and obedient. :( In a previous job I remember problems with a trigger when looking for differences in the before and after images. I never worked on a more convuluted POS in my life. It was the kind of trigger that had an unwritten rota as to which mug of the month got as a prize.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
I dictate the standards on our DBs (mostly) so triggers and underscores struggle to survive and are hunted down and eliminated whenever I get really bored. This does tend to piss off the offending Dev who ignored the conventions. So today they ganged up on me and we will move to MVC instead of WPF for next years crop of apps- bastards!
Never underestimate the power of human stupidity RAH
-
The trigger will file once for each statement. You should always write your triggers with the assumption that the
insertedanddeletedvirtual tables will contain multiple rows.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard Deeming wrote:
insertedRichard Deeming wrote:
deletedBrings back old memories. A stupid bugger rejected me in an interview when I could't answer the question "what are magic tables?". I knew these tables and how to use them, it's just that he expected me to know that they're called magic tables (even after a decade, I haven't heard anyone else calling them by that name).
-
Richard Deeming wrote:
insertedRichard Deeming wrote:
deletedBrings back old memories. A stupid bugger rejected me in an interview when I could't answer the question "what are magic tables?". I knew these tables and how to use them, it's just that he expected me to know that they're called magic tables (even after a decade, I haven't heard anyone else calling them by that name).
Well, by the highly scientific method of Google-fighting:
- sql trigger magic tables[^]: 413,000 results;
- sql trigger virtual tables[^]: 1,680,000 results;
your interviewer was an idiot. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
