October 3, 2002 at 11:09 am
Does anyone know where I can learn why Constraints are better than triggers. BOL explains that they are but do not directly say why. At least not that I can find.
I know triggers are a special kind of stored procedure that SQL Server has to execute.
Do constraints get executed in the same way or some other way that makes them better than triggers (assuming you don't need anything more complex than what a constraint can do)?
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 3, 2002 at 12:43 pm
Does anyone know where I can learn why Constraints are better than triggers. BOL explains that they are but do not directly say why. At least not that I can find.
I know triggers are a special kind of stored procedure that SQL Server has to execute.
Do constraints get executed in the same way or some other way that makes them better than triggers (assuming you don't need anything more complex than what a constraint can do)?
Robert Marda
I can't answer all of your questions at this time, but will offer some personal opinions.
I don't know specifically how a constraint is checked. I would assume (dangerous) that it is an internal trigger.
I like triggers, but will eagerly avoid them if I can. Triggers can provide a more customized constraint if needed, but they can also be very difficult to manage. Especially when they are more than just a constraint and become part of the business logic.
From a database administrative perspective they can make tasks more complicated. You have to be aware of them when working with the data and the tables.
Personally, I would say avoid triggers when you can and use them only when necessary.
Jeff
"Keep Your Stick On the Ice" ..Red Green
October 3, 2002 at 6:40 pm
Also, it has to do with the order of execution. Triggers execute at the point of commit and have to rollback, this is why it can see the inserted and deleted tables. Constraints run at the moment the code executes and do not enter make entries into the inserted and deleted tables as no action took place. Could be off my rocker and if I can find it I will point you to where I found this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 3, 2002 at 6:47 pm
Here is ther SQL BOL version of that
quote:
Microsoft® SQL Server™ 2000 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified. A trigger is invoked in response to an INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.
and
Constraints allow you to define the way Microsoft® SQL Server™ 2000 automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.
In other words the query plan has some information for data validity thus no transaction actually took place on any level other than submit to query engine.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 4, 2002 at 10:12 am
Well, what Antares describes sounds like you can call it an internal trigger (as mentioned by jeffwe). I am going to see if I can capture anything with profiler to try to actually see what SQL Server does when it accepts and rejects something based on a constraint.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 4, 2002 at 6:34 pm
Good luck w/ the research!!!!
I really wish SQL Server would implement BEFORE and AFTER triggers. Granted we have the ROLLBACK, but for design reasons the BEFORE trigger can be useful.
I'm still looking for how to interpret the FirstIAM in sysindexes 🙁 Important stuff w/ me and my EMC.
Jeff
"Keep Your Stick On the Ice" ..Red Green
October 4, 2002 at 6:34 pm
Good luck w/ the research!!!!
I really wish SQL Server would implement BEFORE and AFTER triggers. Granted we have the ROLLBACK, but for design reasons the BEFORE trigger can be useful.
I'm still looking for how to interpret the FirstIAM in sysindexes 🙁 Important stuff w/ me and my EMC.
Jeff
"Keep Your Stick On the Ice" ..Red Green
October 4, 2002 at 6:37 pm
One more idea....
From what Iam reading, the Constraint maybe a BEFORE trigger.
"Keep Your Stick On the Ice" ..Red Green
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply