Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I do a BEFORE UPDATED trigger with sql server?

I'm using Sqlserver express and I can't do before updated trigger. There's a other way to do that?

like image 526
Bigballs Avatar asked Mar 13 '09 13:03

Bigballs


People also ask

Do we have before trigger in SQL Server?

MSSQL does not support BEFORE triggers. The closest you have is INSTEAD OF triggers but their behavior is different to that of BEFORE triggers in MySQL.

What is instead of update trigger?

An INSTEAD OF trigger is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.

Is it possible to create the following trigger before or after update trigger for each row?

A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of new .


2 Answers

It is true that there aren't "before triggers" in MSSQL. However, you could still track the changes that were made on the table, by using the "inserted" and "deleted" tables together. When an update causes the trigger to fire, the "inserted" table stores the new values and the "deleted" table stores the old values. Once having this info, you could relatively easy simulate the "before trigger" behaviour.

like image 73
Stamen Avatar answered Sep 22 '22 23:09

Stamen


MSSQL does not support BEFORE triggers. The closest you have is INSTEAD OF triggers but their behavior is different to that of BEFORE triggers in MySQL.

You can learn more about them here, and note that INSTEAD OF triggers "Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements." Thus, actions on the update may not take place if the trigger is not properly written/handled. Cascading actions are also affected.

You may instead want to use a different approach to what you are trying to achieve.

like image 23
achinda99 Avatar answered Sep 19 '22 23:09

achinda99