Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to Database Design STEP 7: Triggers Expand / Collapse
Author
Message
Posted Monday, December 6, 2010 10:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 PM
Points: 1,945, Visits: 3,173
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
Post #1030720
Posted Friday, September 30, 2011 2:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:46 AM
Points: 69, Visits: 112
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.
Post #1183581
Posted Friday, September 30, 2011 4:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 2, 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
Post #1183634
Posted Friday, September 30, 2011 6:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:57 PM
Points: 1,059, Visits: 1,794

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.
Post #1183699
Posted Friday, September 30, 2011 7:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 1:16 PM
Points: 367, Visits: 303
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.
Post #1183743
Posted Friday, September 30, 2011 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:21 PM
Points: 142, Visits: 134
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.
Post #1183783
Posted Friday, September 30, 2011 11:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:39 PM
Points: 55, Visits: 444
"[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.
Post #1183862
Posted Sunday, October 2, 2011 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 15, 2014 1:58 AM
Points: 18, Visits: 850
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 ...
Post #1184166
Posted Monday, October 3, 2011 5:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 4, 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.
Post #1184964
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse