tags:

views:

266

answers:

2
Q: 

Sql Server 2008 - BEFORE triggers?

Hi all... I've long been waiting for BEFORE triggers. But we have some systems which are written in ORACLE. When a potential new client asked why, our president told them that it was because there are no BEFORE triggers in Sql Server (among other things).

To this, the client responded, "Yes, in 2008 you CAN write BEFORE triggers."

Now I've heard nothing of this, not at the launch events, not in the "what's new" lists online. As far as I know, there still are no proper BEFORE triggers. Am I wrong in this?

Are BEFORE triggers actually hidden in 2008 somewhere?

+2  A: 

SQL Server (since SQL 2k5 I believe) has what's called 'Instead Of' Triggers which is, in a way, a Before trigger.

Charles Bretana  2009-09-02 23:27:53
Yah, INSTEAD OFs *sort* of let you do the same thing, but you wind up having to completely recode whatever SQL statements were supposed to take effect. This leads to LOADS more code in the db, and a MUCH higher potential for bugs to creep in. For example, if a table has a new field added, or one removed, now you need to update the instead of triggers to handle the new (or missing) field. The real power of a BEFORE trigger is for scrubbing your data BEFORE it ever enters the db, resulting in higher data integrity without putting undue burden on the front-end code.
eidylon  2009-09-02 23:37:55
A: 

Your client is wrong :-) Even in SQL Server 2008, there's no "BEFORE INSERT" triggers.

As Charles Bretana already mentioned: you get AFTER INSERT or INSTEAD OF INSERT triggers, but no full-fledged BEFORE triggers in SQL Server.

It's a fact for now - gotta live with it and hope for SQL Server 2008 R2 ! :-)

Marc

marc_s  2009-09-03 05:21:06
That's what I thought. I've been waiting and watching long for these, so I was quite surprised to hear this. I thought they were probably ... mistaken, but I figured I would check anyway. Thanks! :)
eidylon  2009-09-03 19:00:17

related questions

In WPF Style, How can Trigger access a son Element of the Element it is attatched
How to constrain a database table so only one row can have a particular value in a column?
Can you use routed events with a MultiTrigger?
T-SQL - Is there a way to disable a trigger in the scope of a transaction?
Perforce trigger to deny submission of unchanged files?
Triggers vs Constraints
Using WPF, what is the best method to update the background for a custom button control?
Javascript: Trigger action on function exit
Database Duplicate Value Issue ( Filtering Based on Previous Value)
Best Method to Spawn Process from SQL Server Trigger
How to check if a trigger is invalid?
SQL - How do you compare a CLOB
SQL Server 2005 multiple database deployment/upgrading software suggestions
MySQL triggers + replication with multiple databases.
T-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
How do you priortize multiple triggers of a table?
MySQL Trigger & Stored Procedure Replication
Setting Up MySQL Triggers
How can I get the definition (body) of a trigger in SQL Server?
PLS-00306 error on call to cursor
Trigger without a transaction?
Database triggers
Building an auditing system; MS Access frontend on SQL Server backend.
Timer-based event triggers
Throw Error In MySQL Trigger