Exclude Columns from Trigger Update ?

[power97]

Hi,
I was wondering if it would be possible using a trigger to exclude an update if a certain column is updated. For example if we have column A , column B and column C. If I want the trigger update not to fire if column B has been updated can this be done ?
I know that I can do it the opposite way and tell it to update if certain columns have been updated but this is a large table with alot of columns so I would rather exclude. However , I wonder even if excluding works if it would miss cases where column A & B have been updated ( we didn't want it to update for column B )

Thanks

http://www.musicianwanted.ca

 

[JeanNiBee]

Hi

Well you can't specify it programmatically to only 'fire' if a certain field is modified but you can do this.

Note: I say 'modified' but you can pretty much do the same thing for inserted and deleted.

First off each trigger runs in an implicit transaction, when a trigger performs it's work it uses it's own temp tables to manage the data and then commits or rollback the work.

These two temp tables are called "inserted" and "deleted".

Now, something else you want to know is that when a trigger inserts, it puts the new info into the "inserted" tables, makes sure thre are no errors and then commits, effectively taking the info from inserted and actuallyl inserting it into the table.
When you delete info, it uses the temp table called "Deleted" and it does the same. Takes the info being deleted and put it into "Deleted", makes sure there are no errors and commmits, effectively purging the "Deleted" table.
What does an update do? Uses both actually, it takes the information being replace and moves/copies it to the deleted tables, it then take the 'new' information in the 'update' commant and puts it into the "inserted" table. Then if no errors, it purges the "deleted" table and takes what's in the "inserted" Table and puts it into the actualy table being updated.

How does this help you?

Easy.. watch...

Let's assume we have a table called Clients and it looks like this.

CREATE TABLE Clients (
clientID numeric(12,0) identity not null primary key,
name varchar(50) not null,
contact varchar(50) not null,
someFieldThatCanNeverChange varchar(1) not null
)

When you have an update trigger on this table, you can check the "deleted" (before) data and the "inserted" (after) data, if they are different you can either rollback the change (effectively ending the trigger and ignoring the update).

Ex.
CREATE TRIGGER upd_trigger_Clients
ON Clients
FOR UPDATE
AS
BEGIN
DECLARE @check numeric(12,0)
SELECT @check = -1

SELECT @check = D.clientID
FROM inserted I, deleted D
WHERE
LTRIM( RTRIM( I.someFieldThatCanNeverChange ) )
<>
LTRIM( RTRIM( D.someFieldThatCanNeverChange ) )

IF( @check <> -1 )
BEGIN
-- Looks like the information HAS changed, so let's NOT do the update
ROLLBACK
END
ELSE
BEGIN
-- DO whatever else you want to happen when trigger fired
COMMIT
END
END


Hope this helps.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

Last little point...

You did mention you may want to EXCLUDE only some columns from the update.

In effect creating READ-ONLY columns.

There is no way to easily do this, but I am testing some 'tricks' to see if it would work.

I will get back to you on this ASAP.

Thanks.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

Found it.

Inside the trigger, the first commend you should issue is:

SET SELF_RECURSION OFF

This prevents the trigger from being recalled from inside that transaction.

So, you can do the check , if the value are changes, just issue another update setting the column that you don't want changed BACK into the value it originally was, I.E. the value in the deleted table.

Hope this helps.

Don't forget to SET SELF_RECURSION ON at the end of the trigger.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[power97]

Thanks for all the responses but I chose to just do it using the following syntax:

for update as
if update(A)
begin
update .....
end

http://www.musicianwanted.ca

 

[JeanNiBee]

Will this solve your problem?

You can still 'update' those columns you didn't want updated just by issuing an &quot;UPDATE&quot; statement.

I thought you needed a type of read-only column, if so, the trigger syntax you specified won't do that.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects