|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 1,414,
Visits: 1,912
|
|
Comments posted to this topic are about the item Stairway to Database Design STEP 7: Triggers
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 5,
Visits: 22
|
|
You say in a instead of trigger on a view there is no INSERTED and DELETED pseudotable.
So what would an instead of trigger be for ?
I use INSERTED and DELETED pseudotables in instead of triggers and they work fine.
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Monday, April 02, 2012 4:23 AM
Points: 61,
Visits: 12
|
|
We use triggers to audit every insert, update and delete against every table in the database.
We store the audit data in a set of similar tables with extra columns for indicating who did it, when it ws done, where was it done from and what operation was performed. Each Insert, Delete and Update operation results in an insert to the corresponding log table. I cannot see why anyone would think that an "on delete trigger" would try an delete audit data!
The log tables are all protected by further "instead of triggers" for Delete and Update which prevent those operations, and so stop tampering (any attempts at tampering are logged in a tamper table!).
Furthermore we use the CONTEXT_INFO to pass data to the triggers which is not found in the column list of the insert or update statements.
This technique allows us easy access to the data, we can integrate the log tables into the application (as a read-only data source) showing histories for rows such as "orders" in an order table.
So far we have not seen any performance issues.
John
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 472,
Visits: 771
|
|
There is a very easy way to get around any performance issues with auditing done in triggers that no one seems to mention... Service Broker. Compose your message and fire it off with Service Broker and return... no messy writes to disk or transactions to worry about.
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Monday, May 14, 2012 6:02 AM
Points: 365,
Visits: 270
|
|
Joe, Great article as usual. I have a quick question (though maybe long winded). Is declarative referential integrity versus using triggers for referential integrity more efficient or just easier to maintain? I switch from triggers to declarative quite a few years ago, but I've noticed lately (not sure if it is the version of SQL or just the way I'm using it, probably the later) that if I define a Cascading Update/Delete between multiple tables (one table might reference several parent "code/lookup" tables) that SQL complains about multiple update paths and disallows the second cascading update. Don't remember the actual error message, and I'm sure it is probably something dumb I'm doing that it is trying to prevent, still there have been a couple of occassions where I really want the cascade to occur so I'll change from declarative to a trigger. As I recall I've only resorted to this twice and I use the SQL Server option to prevent trigger actions from firing other triggers so haven't run into the endless loop problem.
Back to the original question though which is: Is declarative referential integrity more efficient than triggers (if the trigger is written well) or was this simply for ease of maintenance and clarity in the DDL?
Thanks,
James.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 16, 2012 2:24 PM
Points: 142,
Visits: 93
|
|
Joe,
While I know "It depends" I'm hoping you can steer me in the right direction. What are "good/appropriate" uses for triggers? I've heard all of the reasons not to use triggers, but I've yet to hear the positive ways to use them.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 31,
Visits: 249
|
|
"[views] do not get a DELETED and INSERTED pseudo-table to use. Furthermore, you cannot get a parameter from the INSERT INTO, DELETE FROM or UPDATE statements."
This is not correct: you do get DELETED and INSERTED pseudo-tables in INSTEAD OF triggers on views. I use them all the time.
Also, while you don't have parameters per-se, you can "get a parameter" from the statement: simply add a column to the view such as:
SELECT ..., CAST(NULL AS varchar(100)) AS Param1
In this way, Param1 will be returned as a null column when you select from the view, but when you insert or update the INSTEAD OF trigger can inspect the value of Param1 in the pseudo tables.
But otherwise a great article! I especially appreciate and agree with the sentiment that you should avoid using triggers if possible.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 7,
Visits: 368
|
|
john-645829 (9/30/2011)
The log tables are all protected by further "instead of triggers" for Delete and Update which prevent those operations, and so stop tampering (any attempts at tampering are logged in a tamper table!).
Now you need a tamper table 2 to log attempts to tamper with the tamper table, and also tamper table 3 to log attempts to tamper with tamper table 2, and ...
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, October 04, 2011 5:21 PM
Points: 3,
Visits: 8
|
|
| It is a pity the ANSI/ISO Standards Committee did not understand the original definition and implementation of triggers for relational databases as implemented by IBM under Ted Codd's model on S38/AS400 wherein each record is presented separately (and through a greater range of trigger types). Basically, the ANSI/ISO model is just poor.
|
|
|
|
