triggers before insert and row level.
Need to have real Before triggers and row level triggers. SQL doesn't have these triggers or sequences. They have "instead of" but not a real Before Trigger like enterprise level databases have. It's a real pain to program in SQL or to convert from Oracle or MySQL.
Upvotes: 43<=-=Jul 3 2007 12:12AM=-=>
BEFORE triggers are part of ANS SQL 99 (and 2003), and as such have been implemented by many manufacturers (Oracle, IBM).
It’s time MS implemented ANS SQL.<=-=Jul 8 2007 5:49AM=-=>
That request is for 3 features instead of one!
1) I agree about Before Triggers
2) Row triggers are easily done with SQL although with non ANSI syntax. But they are a bad practice, so low priority for me.
3) Sequence would be nice eventually, but it is not a show stopper.
Thank you for posting your request. We realize that BEFORE triggers are extremely useful, specifically in migration scenarios. Unfortunately, we are not able to provide this functionality in SQL Server 2008. We are considering it for a future release. Meanwhile, we suggest using INSTEAD OF triggers to achieve similar functionality.<=-=Aug 17 2007 1:01PM=-=>
INSTEAD OF is not a workable replacement for BEFORE.
Trigger support needs to be: one INSTEAD OF, or any number of BEFORE and AFTER with sequence control.<=-=Jul 16 2008 6:39AM=-=>
I agree we need true BEFORE Triggers. Row level triggers, I would have to say NO, SQL is a SET based language<=-=Jul 16 2008 7:11AM=-=>
Yes, it’s time for it<=-=Aug 6 2008 2:15PM=-=>
Yes to BEFORE TRIGGERS, this is long overdue. Per row-triggers? Maybe they work well in Oracle,
but I would suspect they would be a performance killer in SQL Server. So you need to rewrite those
Oracle triggers anyway.
As a business developer using Oracle with Microsoft development tools, MS SQL server lack of support for row-level triggers changes “migrate” to “re-write” and “maybe” to “no way” Like many small ISVs we would love to have the chance (time/money) to re-write all of our applications but re-write something that works well vs make new features or modules to our applications … its just bad business and you can go out of business thinking that way.<=-=Feb 7 2014 3:36PM=-=>
I have been needing BEFORE TRIGGERS for a long time, too. We have some complex integrity constraints, and it would be a lot cleaner and more efficient to block an action than roll it back afterwards.<=-=Mar 17 2017 12:29PM=-=>
Please see my “BEFORE TRIGGER Behavior with EXECUTE ORIGINAL_STATEMENT” topic.
Maurice Pelchat commented
Well don't do that. I worked with both SQL Server (After triggers and Before triggers), and Oracle row triggers. I have some insight on that. If this come to existence, it will allows to write a lot of inefficient server code on SQL Server.
Oracle row triggers have the unpleasant limitation that comes with mutating table errors, and this is for some reason: There is an underlying database consistency risk, so Oracle blocks it.
This feature is just a way to hide cursor style programming. SQL Server syntax often allows to avoid this. It allows to join inserted and deleted table with real data, for multi-row operations and also allows to use Apply clause to compute fairly complex business logic. So SQL Server trigger fire only once for many rows, if set based logic is used everywhere in nested trigger execution. This is much faster that what happen on other SQL platform that use row triggers.
When working in a row-by-row fashion, triggers fire at each row, which makes trigger code inefficient. Anyway, working in a row-by-row mode makes poor performing SQL code. Row-by-row processing makes life easier for programmers who do not know how to do set logic in SQL. So row-by-row triggers will eventually allow to them to write a lot of inefficient SQL code.
Efficient business logic in set query is possible often without big complexity. SQL allows to use easily pseudo-tables inserted/deleted in joins with other tables, and even joins in updates,deletes and inserts/select. The APPLY clause (used in the FROM clause) allows to compute fairly complex business logic into queries. This is why SQL Update/Delete syntax allows the use of clean join expressions into them to get the data to modify.