How to override values in triggers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to override values in triggers

Author

Topic

jsdude99
Starting Member

8 Posts

Posted - 2008-07-09 : 00:25:30

Hi,
I have a binary field in one of the tables and when I make an update to the table (single row at the moment but thinking about multiple rows as well), I want to perform some checks before the update and if necessary, I want to override the user input. Since there are more than one stored procedure that updates the table, I want to implement that using a trigger. However, I could not work out the syntax.
As a simplified example, assume I have a Quantity field (integer) and Active flag (bit) in the table (plus primary key field and 20+other fields). I also have another table (Orders), which holds the orders. In my trigger, I want to override the Active flag to TRUE if the Quantity of the item is greater than zero OR the Orders table has an entry for the item.
Any help will be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 00:47:58

something like this:-

CREATE TRIGGER YourTrigger ON YourTable
AFTER UPDATE
AS
UPDATE t
SET t.Active=CASE WHEN t.Qty >0 OR o.Key IS NOT NULL THEN 1 ELSE 0 END
FROM YourTable t
INNER JOIN INSERTED i
ON i.PK=t.PK
LEFT JOIN Orders o
ON o.Key=t.Key

jsdude99
Starting Member

8 Posts

Posted - 2008-07-11 : 00:51:15

Thanks very much visakh16, this was an excellent answer. Sorry about my late thanks as I did not receive an email when you answered the question. I will now make sure I subscribe the topic.
A follow-up question, if the command was insert, how would you make sure to turn the Active flag always on?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 01:12:16

quote:
Originally posted by jsdude99

Thanks very much visakh16, this was an excellent answer. Sorry about my late thanks as I did not receive an email when you answered the question. I will now make sure I subscribe the topic.
A follow-up question, if the command was insert, how would you make sure to turn the Active flag always on?

for that you need insert trigger just change UPDATE to INSERT

CREATE TRIGGER YourTrigger ON YourTable
AFTER INSERT[s] UPDATE[/s]
AS
UPDATE t
SET t.Active=CASE WHEN t.Qty >0 OR o.Key IS NOT NULL THEN 1 ELSE 0 END
FROM YourTable t
INNER JOIN INSERTED i
ON i.PK=t.PK
LEFT JOIN Orders o
ON o.Key=t.Key

and if you want for both put both comma seperated

jsdude99
Starting Member

8 Posts

Posted - 2008-07-11 : 06:31:39

Thanks again, but I want to have different actions for each. That is, the update action will be the one above but insert will always set the flag to 1, without any condition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 06:44:43

quote:
Originally posted by jsdude99

Thanks again, but I want to have different actions for each. That is, the update action will be the one above but insert will always set the flag to 1, without any condition.

for implementing this, you dont need a trigger. you only need to define a default constraint on flag column to set it to 1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources