Using the current record within a 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)

Using the current record within a trigger

Author

Topic

phila
Starting Member

3 Posts

Posted - 2002-08-08 : 05:37:19

Hi,
We have an update trigger on column A in Table 1. We want the trigger to update column B with the value from column C IF column D is a certain value.

When we syntax check the code it complains that column C and D are unknown to the query, so how can we referance the row in the table where column A has been been updated within the trigger?

We are using SQL 2000 BTW.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-08 : 06:56:33

The rows affected by a statement are available in the tables deleted and inserted within a trigger. In the case of an update statement deleted containts the rows as the were before the update and inserted contains the new values.

Could you show the pertinent code for your trigger and the ddl statetment for the table. It is a bit pointless guessing what may cause the error.

OT: Why do you need the trigger at all? This surely could be handled when presentating the data.

phila
Starting Member

3 Posts

Posted - 2002-08-08 : 07:04:16

Here is the code we are using in the tigger:

CREATE TRIGGER PX_UPDATE_C29_to_C30 ON dbo.t_partex
FOR INSERT, UPDATE
AS

--'Columns Status (C19) and achieved sp (C26)
IF SUBSTRING(COLUMNS_UPDATED(), 3, 1) = POWER(2,(3-1))
OR
SUBSTRING(COLUMNS_UPDATED(), 4, 1) = POWER(2,(2-1))

BEGIN
IF so_c19_status = 'US'

BEGIN --C29
UPDATE t_partex SET rss_c29_unsold = ISNULL(io_c26_achieved_selling_price,0)
PRINT 'C29 Updated'
END
END

The syntax check fails on the line IF so_c19_status = 'US'

Cheers
Phil

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 07:46:08

You are missing some critical theory begin triggers. If you don't understand exactly why this code below works, then post back so someone can help you clear up your misconceptions.


CREATE TRIGGER PX_UPDATE_C29_to_C30 ON dbo.t_partex 
FOR INSERT, UPDATE 
AS 

--'Columns Status (C19) and achieved sp (C26) 
IF SUBSTRING(COLUMNS_UPDATED(), 3, 1) = POWER(2,(3-1)) 
OR 
SUBSTRING(COLUMNS_UPDATED(), 4, 1) = POWER(2,(2-1)) 

BEGIN 
UPDATE t_partex SET rss_c29_unsold = ISNULL(io_c26_achieved_selling_price,0) 
from inserted i inner join t_partex t on i.primarykey = t.primary key
where i.so_c19_status = 'US'
PRINT 'C29 Updated' 
END

Jay White
{0}

phila
Starting Member

3 Posts

Posted - 2002-08-08 : 09:02:32

Thanks for that, it now works a treat. Previously we where unaware of the inserted and deleted tables for an update.

Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources