Compare old and new values in trigger

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

SQL Server 2000 Forums

SQL Server Development (2000)

Compare old and new values in trigger

Author

Topic

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-11 : 07:48:07

Hi,
Here is the scenario.

When an update happens to a row, I need to check if the new amount value is greater than the old value. If not, the update should be nullified.

Here is what i tried.

CREATE TRIGGER Trig_TXNMaster_Amount_Update
ON TXNMaster
FOR UPDATE
AS BEGIN
UPDATE inserted
SET TXNMaster.AMOUNT = b.AMOUNT
FROM inserted a, deleted b
WHERE a.AMOUNT<b.AMOUNT
END

Problem here is it updates all the rows of the table.

So, i tried

CREATE TRIGGER Trig_TXNMaster_Amount_Update
ON TXNMaster
FOR UPDATE
AS BEGIN
UPDATE inserted
SET inserted.AMOUNT = b.AMOUNT
FROM inserted a, deleted b
WHERE a.AMOUNT<(1.1*b.AMOUNT)
END

This gives an error that
"The logical tables INSERTED and DELETED cannot be updated."

Please help me in achieving this functionality.

Thanks,
Jeeves

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 07:52:48

[code]CREATE TRIGGER Trig_TXNMaster_Amount_Update
ON TXNMaster
FOR UPDATE
AS
BEGIN
UPDATE m
SET AMOUNT = b.AMOUNT
FROM TXNMaster m
INNER JOIN inserted a ON m.ID = a.ID
INNER JOIN deleted b ON m.ID = a.ID
WHERE a.AMOUNT > b.AMOUNT
END[/code]

or do you want raise error to rollback the update ?

KH
[spoiler]Time is always against us[/spoiler]

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-11 : 08:03:12

Thanx for the reply.

But,
I don't have a primary key in TXNMaster to do an inner join :(

Error plus Rollback would be gr8. But not high priority.

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-11 : 08:17:53

is it possible for me to have a direct condition like
If( inserted.AMOUNT > deleted.AMOUNT) ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 08:37:26

quote:
Originally posted by gv_pradeep

Thanx for the reply.

But,
I don't have a primary key in TXNMaster to do an inner join :(

Error plus Rollback would be gr8. But not high priority.

use the column that uniquely defines your table in that case instead of PK

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-11 : 12:12:43

Hi...Thanx for al ur replies..I achieved it this way...Let me know if there is any efficiency lag.

CREATE TRIGGER Trig_TXNMaster_Amount_Update1
ON TXNMaster
FOR UPDATE
AS BEGIN
DECLARE @nOldValue int,@nNewValue int
SELECT @nOldValue=b.AMOUNT, @nNewValue=a.AMOUNT -- Get the Old and New values
FROM inserted a, deleted b
IF @nNewValue > @nOldValue
BEGIN
PRINT 'Transaction Error'
ROLLBACK TRAN -- Roll Back the transaction if condition is not satisfied
END
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 13:21:09

You've not specified a condition for joining inserted and deleted. Please note that you inserted deleted tables may contain more than a single record. In that case what is your rule to determine which value of inserted needs to be joined to which value of deleted?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 22:03:31

quote:
I don't have a primary key in TXNMaster to do an inner join :(

How is it possible that you don't have a Primary Key in your table ? Then how do you update the table TXNMaster ?

Post your update statement here

KH
[spoiler]Time is always against us[/spoiler]

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-12 : 01:16:29

Hi Visakh, You are right. This will work fine in case of a single update. But will fail where there are multiple records update :(
Hi Khtan, I'm updating based on a combination of two columns which are unique. I can use this same combination for the inner join,I guess. Is there any way to rollback the transactions in case of multiple updates.?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-12 : 01:40:26

use RAISERROR to rollback the update.

Why are you using TRIGGER in the first place ? You can very well perform the necessary check in your update statement


update t
set    value = @newvalue
from   yourtable
where  value < @newvalue

KH
[spoiler]Time is always against us[/spoiler]

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-12 : 01:48:04

I'm trying to enforce a rule such that nobody should be able to update the table wrongly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources