Using Triggers to update a field in a table 1

[tbaguio]

Hello,

I have 1 table that looks like this

Table XItem {
TransactID
TransactStatus nVarchar
TransactStatusId Int

This is all done in a MS SQL 2000 backend


What I want to do is when a user updates either TransactStatus or TransactStatusID I would like the field to update the the other field as well all with Triggers

for example

If TransactStatus = 1 then TransactStatusID = 1
If TransactStatusID = 2 then TransactStatus = 2

If you are wondering why I would want to do something like this, its a small patch/workaround and handy to know in the future

Thanks

PS this is the trigger code i got so far but it doesn't work

CREATE TRIGGER XItemActionsBandAid ON [dbo].[XItemActions]
FOR UPDATE AS

If update(TransActionStatus)
update X set X.TransActionStatusID = 2 from XItemActions X WHERE X.TransactionID <> (Select TransactionID from inserted)












&quot;Sleep is the best meditation.&quot; - Dalai Lama

 

[zhavic]

Try something like this:

ALTER TRIGGER XItemUpdate ON XItem
FOR UPDATE
AS
/* set TransactStatusId = TransactStatus */
IF UPDATE( TransactStatus ) AND TRIGGER_NESTLEVEL( object_ID( 'XItemUpdate' ) ) = 1
UPDATE XItem SET XItem.TransactStatusId = inserted.TransactStatus FROM inserted WHERE XItem.TransactID = inserted.TransactID

/* set TransactStatus = TransactStatusID */
IF UPDATE( TransactStatusId ) AND TRIGGER_NESTLEVEL( object_ID( 'XItemUpdate' ) ) = 1
UPDATE XItem SET XItem.TransactStatus = inserted.TransactStatusId FROM inserted WHERE XItem.TransactID = inserted.TransactID


-------------------------------------------------
If you have 'Recursive triggers' option turned off, you don't need this part of code ( in both IF statements ):
AND TRIGGER_NESTLEVEL( object_ID( 'XItemUpdate' ) ) = 1

Note, that this example will not work, if you UPDATE both columns ( TransactStatus and TransactStatusId ) at the same time,
e.q. in one UPDATE statement.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[tbaguio]

Thanks very much for your reply. Your assistance was greatly appreciated. That did the trick!

More proof that the posters on Tek Tips know their stuff!

&quot;Sleep is the best meditation.&quot; - Dalai Lama