USING IF UPDATED ON TRIGGERS - SQL Server Forums

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

Transact-SQL (2000)

USING IF UPDATED ON TRIGGERS

Author

Topic

condealmasy
Starting Member

1 Post

Posted - 2004-09-30 : 18:15:19

Hello.

I've programmed in Transaql Sql for some time, specially with Stored Procedures. I use Sql Server 2000

I have to create a Trigger that occurs when I update a particulary Column on a Table. If that column if update then I must get the ID from that row and use it. I'm really confused, How can I know what is the value of another column in the row which the updated column is.

For example:

CREATE TRIGGER [UpdOrder] ON [dbo].[Item_Order]
FOR UPDATE
AS

IF UPDATE (isOrder)
BEGIN
If isOrder = 1
Begin
Select @counter = Count(orderID) from Item_Order where isOrder = 1 and itemID = (itemID2 = itemID from the updated row)
End
END

Select @counter

Notice that isOrder is a boolean (bit), and I need to know that when I update it to true, find the number of rows in the table. itemID is the other column in the table, itemID2 is not a column is actually the same itemID, but this one is the value from the row that has been updated.

That should do it but is not working.

Maybe I'm missing something here, maybe I cannot do this crazy trigger.

Does anyone have a suggestion?.

Thanks in advanced.

JPD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-30 : 18:37:22

When the trigger fires on UPDATE, each row affected by it is contained in a trigger table called inserted. So you can find the value of your ID column by calling that table. The problem though is that this table is going to contain more than one row if the UPDATE statement affected more than one row. So you can't use variables unless you loop which is not recommended. Can you show us an example of what you are trying to do and then we'll be able to come up with a set-based trigger solution for you?

Tara

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 03:20:22

i think what you're looking for is columns_updated(), this will check if a column has been updated, just specify the column you need to check (search BOL for further details).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 12:23:42

jen,

IF UPDATE(ColumnName) is what he is looking for. columns_updated() is used to test which column has been updated. But he already know which one he wants, so UPDATE(ColumnName) is what he wants.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources