trigger instead of update - 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 2005 Forums

Transact-SQL (2005)

trigger instead of update

Author

Topic

tdozier
Starting Member

4 Posts

Posted - 2009-07-13 : 13:12:26

I need to setup a trigger (instead of update) that updates all columns with the exception about 20 historic fields. Basically I would like to either specify an update that omits updating these historic columns or to pull the data from the table, update the data and then update the columns again with the historic data.

Any help is appreciated.

LeeM01
Starting Member

1 Post

Posted - 2009-07-13 : 13:42:18

You can try something like this:

CREATE TRIGGER dbo.NewUpdate
ON MyTable
INSTEAD OF UPDATE
AS
IF @@ROWCOUNT = 0 RETURN

SET NOCOUNT ON

UPDATE MyTable
SET
field1 = ii.field1
, field2 = ii.field2
....(list all the fields you want to update here, leave out the ones you don't want to update)
FROM inserted AS ii
WHERE ii.PrimaryKey = MyTable.PrimaryKey

RETURN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:48:07

quote:
Originally posted by tdozier

I need to setup a trigger (instead of update) that updates all columns with the exception about 20 historic fields. Basically I would like to either specify an update that omits updating these historic columns or to pull the data from the table, update the data and then update the columns again with the historic data.

Any help is appreciated.

whats the condition that determines whether you need to omit historic data or not?

tdozier
Starting Member

4 Posts

Posted - 2009-07-13 : 14:07:23

LeeM01, Thanks for your quick response. I will start playing with your code.

visakh16, The condition would always be true on an update, I don't have access to the code that updates the table, or I would modify the original code. I am left with this option of using a trigger.

tdozier
Starting Member

4 Posts

Posted - 2009-07-13 : 15:41:37

I feel confident that the code will work, but when entering my primary key in the where statement I get an error of Ambiguous Column name.

Any suggestions?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 15:50:57

>>I get an error of Ambiguous Column name.
This error means you are not qualifying the column with the table alias (or table name)
You should not mix using table aliases for some tables and not others. I would implement LeeM01's code like this:


UPDATE mt set
       mt.field1 = i.field1
       ,mt.field2 = i.field2
       --...
FROM inserted AS i
inner join MyTable mt 
       on mt.<PK> = i.<PK>

So once this trigger is in place the only way to write to those historic columns is during INSERT. You'll never be able to update them without dropping/disabling this trigger. Is that what you want?

Be One with the Optimizer
TG

tdozier
Starting Member

4 Posts

Posted - 2009-07-14 : 06:59:37

Thanks everyone, that did the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources