Web Development
by jwc1410 Accepted solution
by tony hopkinson Accepted solution
When an Update trigger fires
the the changed record(s) are in the deleted table and the new version(s) of them in the Inserted table.
At this point you have access to the primary key value(s) in the triggered table.
Obviously you can't use this data to twiddle with the table with the trigger on it, that would trigger another trigger ... :D
You can mess with inserted for validations, data cleaning and of course logging and cascading, (eg force creating a blank order line on a new order or some such).
A tip with triggers they are very nice, very powerful and a really quick route to the deep brown stuff if you go mad with them.
by zeroesque Accepted solution
"Obviously you can't use this data to twiddle with the table with the trigger on it, that would trigger another trigger ... "
I believe recursive triggers are turned off by default at the database level in SQL Server.
EXEC sp_dboption '', 'recursive triggers'
by tony hopkinson Accepted solution
Limit is 32 I think.
Hit that once by accident Table1 insert trigger to Table 2 , Table2 to Table1 :p
You get told off for that one now I believe.
Haven't done it since.
One of the classics is to get a trigger to send an email, which can take a heck of a long time.
Triggers are great as long as you remember a change isn't complete until all the triggers have returned.
SQL 2005 has some great options for using MSMQ, or the old stand by of having an action table where you store what happened and deal with it later can be used.
I used to use that method a lot, simple and very robust.
If at all possible avoid chaining triggers is always my advice.
by howyue Accepted solution
by Member 22070467 Accepted solution
by Member 22070467 Accepted solution
Ive actually never ran into a situation where I have created circular references with triggers, and I've written a lot of them. I don't particularly love triggers, but they can be very useful when necessary...such as checking for business rules after data has been updated. Such as not allowing certain thresholds to be reached in the database.
As in reference to your MSMQ comment...are you referring to Service Broker? If not, have you used SB? It is very handy for doing just what you described, but in the database.
by tony hopkinson Accepted solution
I have some vague memory from the course I did, underlying tech was MSMQ. However the point was that you could easily generate asynchronous tasks.
I used triggers to fill tables of items changed, then an other task bundled them up and FTP'd them to a HP Mini where the legacy ordering system was for instance.
Not something you want to wait around for in the middle of an update. :D
Nowadays I'd use one of the built in mechanisms, back in SQL Server 6.0 days the support wasn't good enough and the tech too fragile to risk.
The advantage of doing it in triggers is of course you don't have to worry about, 'queueing' the task explicitly, so it never gets missed by a coder.
Th reason I really liked them was for monitoring, and generating adhoc reports.
They are a good way of avoiding having to use cursors when you want to capture state changes between updates and such.
Who or what made a change to a particular pattern of data
A little dash board table, to collect simple stats on what's been without running a bunch of aggregate queries on your extermely dynamic tables.
Not doing any of this now all, on the coding side.
That's going to change soon though, just been given a new project, total revamp of the existing database schema.
Have to brush my skills up again, bit rusty.
:(
by Member 22070467 Accepted solution
This discussion is closed to new replies.
Sort by Trending