Trigger on a column - 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)

Trigger on a column

Author

Topic

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 15:27:11

My objective is to record the old and new values of a column with the column name and the table name when that column gets changed in a transaction table.

I am trying to do this in a table which has 36 columns. I did read about the "if update(column_name)" and also about the "columns_updated()" functions.

Is syscolumns the only way of getting the column names?
What is the clean way and how should I go about this? Should I code everything in my trigger or should I call a SP from the trigger to get the column names from syscoluns or hardcode all the column names in the trigg which will make it faster but will be a problem if I add a column later to that table.

So please advise and I would appreicate any suggestions.

P.S Also what will be the performance hit due to this?

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 15:44:28

Any of these will work.
I would hard code the column names and leave all the code in the trigger and generate the trigger from the table. If you change the table structure then you just need to generate the trigger again.
Note that this will save you problems with insrted and deleted tables not being available in different batches.

Also you will probably need to check old and new values of the column as just an update of the field may not mean that it has changed.

syscolumns or information_schema views are the ways to get column names.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 16:11:26

Hi NR,

Ok, then is it good to write a trigg with all column names hardcoded and then write something like
"if (substring(column_updated(),1,1))&1 > 0 " it means that the first column got updated. In my case I have 36 columns which means that I will have 5 bytes of 8 bits each. Now if I have updated 8,16,24,32,36 the columns what will be my column_updated value.

I know that the varbinary will look like this
00000001 00000001 00000001 00000001 0001000
(8) (16) (24) (32) (36)

what will be my column_updated value? and how should I get the values that it is the (8) (16) (24) (32) and (36)? I guess I should loop and get it but how though? I am still not clear.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 16:35:39

It's always worth testing it to see what happens.

drop table trigtest
go
create table trigtest
(
i1 int ,
i2 int ,
i3 int ,
i4 int
)
go
insert trigtest select 1,1,1,1
go
drop trigger tr_trigtest
go
create trigger tr_trigtest on trigtest for update
as
declare @i int ,
@j int
select @j = -1
while @j <= (select max(colid) from syscolumns where id = (select id from sysobjects where name = 'trigtest'))
begin
select @j = @j + 1
select @i = power(2,@j)
if COLUMNS_UPDATED() & @i > 0
select @i,@j, name from syscolumns where colid = @j + 1 and id = (select id from sysobjects where name = 'trigtest')
end
go

update trigtest set i1 = 2
update trigtest set i2 = 2
update trigtest set i3 = 2
update trigtest set i4 = 2

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 16:55:32

If you run this

declare @table varchar(128)
declare @col varchar(128)
select @table = 'trigtest'

set nocount on
declare @s varchar(1000)

print 'drop trigger tr_' + @table
print 'go'
print 'create trigger tr_' + @table + ' on ' + @table + ' for update'
print 'as'

declare @i int, @j int
select @j = -1
while @j < (select max(colid)-1 from syscolumns where id = (select id from sysobjects where name = @table))
begin
select @j = @j + 1
select @col = name from syscolumns where colid = @j + 1 and id = (select id from sysobjects where name = @table)
select @s = 'if update(' + @col + ')'
print @s
select @s = 'select ''' + @col + ''' , ' + @col + ' from inserted'
print @s
end
print 'go'
print ''

the result will be

drop trigger tr_trigtest
go
create trigger tr_trigtest on trigtest for update
as
if update(i1)
select 'i1' , i1 from inserted
if update(i2)
select 'i2' , i2 from inserted
if update(i3)
select 'i3' , i3 from inserted
if update(i4)
select 'i4' , i4 from inserted
go

If you then run that you will get a trigger that will select the column name and new value for any updated field.
If you change the select to an insert, do the same with deleted, make it a character value (doubling any embedded quotes for character fields - get field type frm syscolumns) then you probably have it.
Note that you may still want to cater for a field being updated but to the same value.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/21/2002 16:57:30

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 17:32:01

Hi NR,

Thanks for your time. Really appreciate it. I have a question though..

Here is what I am trying to do

drop trigger upd_req_trig
go
create trigger upd_req_trig on req for update
as
declare @i int,@j int,@trans_seq_num int,@as_new_val varchar(25),@as_old_val varchar(25),@col_name varchar(25)
select @j = -1
while @j <= (select max(colid) from syscolumns where id = (select id from sysobjects where name = 'req'))
begin
SELECT @trans_seq_num = max(seq_nbr) FROM doc_trans
set @trans_seq_num = isnull(@trans_seq_num, 0)+1

select @j = @j + 1
select @i = power(2,@j)
if COLUMNS_UPDATED() & @i > 0
select @col_name = name from syscolumns where colid = @j + 1 and id = (select id from sysobjects where name = 'req')
--select @i,@j, name from syscolumns where colid = @j + 1 and id = (select id from sysobjects where name = 'req')

Select @as_new_val = Inserted.@col_name from Inserted
Select @as_old_val = Deleted.@col_name from Deleted

INSERT INTO doc_trans
(seq_nbr,
old_value,
new_value,
table_name,
column_name)
values(@trans_seq_num,@as_old_val,@as_new_val,'req',@col_name)

end
go

When I compile this I get this
"Server: Msg 170, Level 15, State 1, Procedure upd_req_trig, Line 16
Line 16: Incorrect syntax near '@col_name'.
Server: Msg 170, Level 15, State 1, Procedure upd_req_trig, Line 17
Line 17: Incorrect syntax near '@col_name'."

Those two lines are this below

Select @as_new_val = Inserted.@col_name from Inserted
Select @as_old_val = Deleted.@col_name from Deleted

Am I confused or not seeing the picture here? Where am I going wrong?

Please "sir" help

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 17:39:44

select col from table is the syntax.
The compiler cannot resolve the object name if it is in a variable.

That's why I did the generation script

Normally you could

select @sql = 'select ' + @col + ' from table'
exec (@sql)
but as I mentioned inserted and deleted are only available withing the trigger batch so this does not work.

You could
select * into #inserted from inserted
select * into #deleted from deleted

then use #inserted and #deleted in the dynamic sql - up to you whether you think this is too much overhead.
I would go with the trigger generation script.

If you go with the temp tables and dynamic sql you will need to use sp_executesql to set the variable - see www.nigelrivett.com for how to do this.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/21/2002 17:42:31

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 17:50:17

Thanks!! I did try that and the trigger got compiled without any errors. Now the problem is when I tried to update any column in the table req through PB I get an arithmetic overflow error occured.

Still out there trying to make it work

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 17:59:59

Where does the overflow happen?

Which one did you run?
The one which uses if updated() shouldn't have any problems.
The columns_updated() will get that error if you have more than 32 columns as the bit map won't fit in an int.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 18:31:00

I ran the columns_updated. Yes my table has 34 columns and that proably is the reason huh? Any way I am going to try with the if update..
Will let you know

Thanks

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 18:50:16

"Here it is.. This is the new code with update"

drop trigger upd_req_trig
go
create trigger upd_req_trig on req for update
as
declare @i int,@j int,@trans_seq_num int,@as_new_val varchar(25),@as_old_val varchar(25),@col_name varchar(50)
declare @tab_id int,@column_id int
select @j = -1
SELECT @trans_seq_num = max(seq_nbr) FROM doc_trans
set @trans_seq_num = isnull(@trans_seq_num, 0)
select @tab_id = id from sysobjects where name = 'req'
select @column_id = max(colid) from syscolumns where id = @tab_id

while @j <= @column_id
begin
set @trans_seq_num = @trans_seq_num + 1

select @j = @j + 1
select @col_name = name from syscolumns where colid = @j + 1 and id = @tab_id
if update(@col_name)

Select @as_new_val = @col_name from Inserted
Select @as_old_val = @col_name from Deleted

INSERT INTO doc_trans
(seq_nbr,
old_value,
new_value,
table_name,
column_name)
values(@trans_seq_num,@as_old_val,@as_new_val,'req',@col_name)

end
go

The problem now is it says
"Line 20: Incorrect syntax near '@col_name'."

I dont understand what the problem is here?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:04:09

If you want to use columns_updated() then you can use a substring command to get the correct bits.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:05:29

I did some test code for the columns_updated() problem

drop table trigtest
go
create table trigtest
(
i01 int
)
declare @i int, @s varchar(1000)
select @i = 1
while @i < 50
begin
select @i = @i + 1
select @s = 'alter table trigtest add i' + right('00' + convert(varchar(2),@i),2) + ' int'
exec (@s)
end
go

declare @i int, @s varchar(1000)
select @i = 0
while @i < 50
begin
select @i = @i + 1
select @s = coalesce(@s + ',1', 'insert trigtest select 1')
end
exec (@s)
go

drop trigger tr_trigtest
go
create trigger tr_trigtest on trigtest for update
as
declare @bit int ,
@field int ,
@char int
select @field = 0
while @field < (select max(colid) from syscolumns where id = (select id from sysobjects where name = 'trigtest'))
begin
select @field = @field + 1
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
--select @char, @field, @bit
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
select @field, name from syscolumns where colid = @field and id = (select id from sysobjects where name = 'trigtest')
end
go

set nocount on
update trigtest set i08 = 2
update trigtest set i41 = 2
update trigtest set i03 = 2
update trigtest set i40 = 2
update trigtest set i01 = 2

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:13:40

The problem you still have is the syntax for accessing a table is
select col from table
select @col from table
doesn't work (see my previous response).

To do what you are trying to do

declare @s varchar(1000)
select @s = 'select ' + @col + ' from inserted'
exec (@s)

As I said though inserted is not available in the exec batch so you would need to
select * into #inserted from inserted.

then you cannot set a variable from dsql like that, you need sp_executesql.
something like this

declare @s nvarchar(1000)
declare @var nvarchar(1000)
select @s = 'select @var = ' + @col + ' from #inserted'
exec (@s)
exec sp_executesql @s, N'@var varchar(100) output', @var output
select @var

You will also need to look at the column type to see if it needs conversion

That's why I generated the script to use all hard coded column names.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 19:41:33

Duh! now I understood why you are doing like that.. I am going to check that out and will let you know

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 19:53:32

Alright I thought I will try and make the columns_updated work and I gave it a shot with your recommendations. Here is the deal... Everything compiles fine, but when I try to update the table it just hangs. There is no error message but it looks like its going in a loop or something. Could you please check this and see where I am goofing up. My brains are fried and I think if you look at it maybe I will have a chance of going home sometime

Here it is

drop trigger upd_req_trig
go
create trigger upd_req_trig on req for update
as
declare @i int,@j int,@trans_seq_num int,@as_new_val varchar(25),@as_old_val varchar(25),@col_name varchar(25)
declare @tab_id int,@column_id int
declare @bit int, @field int,@char int
select @j = -1
SELECT @trans_seq_num = max(seq_nbr) FROM doc_trans
set @trans_seq_num = isnull(@trans_seq_num, 0)
select @tab_id = id from sysobjects where name = 'req'
select @column_id = max(colid) from syscolumns where id = @tab_id

while @j <= @column_id
set @trans_seq_num = @trans_seq_num + 1

select @field = 0
while @field < (select max(colid) from syscolumns where id = (select id from sysobjects where name = 'trigtest'))
begin
select @field = @field + 1
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
--select @char, @field, @bit
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
select @col_name = name from syscolumns where colid = @field + 1 and id = @tab_id
--select @i,@j, name from syscolumns where colid = @j + 1 and id = (select id from sysobjects where name = 'req')
Select @as_new_val = @col_name from Inserted
Select @as_old_val = @col_name from Deleted
INSERT INTO doc_trans
(seq_nbr,
old_value,
new_value,
table_name,
column_name)
values(@trans_seq_num,@as_old_val,@as_new_val,'req',@col_name)

end

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:55:30

while @j <= @column_id
set @trans_seq_num = @trans_seq_num + 1

looks like the problem - you need to get out of the loop sometime.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 19:58:09

You will still need to change

Select @as_new_val = @col_name from Inserted
Select @as_old_val = @col_name from Deleted

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 22:57:52

I tried different combinations but still I have a question
right here.
-- Select @as_new_val = @col_name from Inserted
-- Select @as_old_val = @col_name from Deleted

I am trying to get the equivalent of the above lines

"I understand the next two lines"

select @sql = 'select ' + @col + ' from inserted'
exec (@sql)

After exec @sql how can I get the @as_new_val? Thats where I am not following your example.

"The next five lines I am not clear. Could you please explain?"
but as I mentioned inserted and deleted are only available withing the trigger batch so this does not work.

You could
select * into #inserted from inserted
select * into #deleted from deleted

then use #inserted and #deleted in the dynamic sql - up to you whether you think this is too much overhead.

Thanks much!!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 23:04:30

You cannot use a variable containing a column name to access a table without using dynamic sql (exec (@sql)).
The trigger virtual tables inserted and deleted are not available outside the batch scope of the trigger - i.e. not available to the exec'd statement.

You can make copies of these tables into temp tables which wilkl be available in the dsql.
Hence
select * into #inserted from inserted
select * into #deleted from deleted

To get the values into variables you will have to use sp_executesql
Hence
declare @s nvarchar(1000)
declare @var nvarchar(1000)
select @s = 'select @var = ' + @col + ' from #inserted'
exec (@s)
exec sp_executesql @s, N'@var varchar(100) output', @var output
select @var

Still think it's easier to use the trigger generation script so all the column names will be hard coded.
Have you thought what will happen if ever more than one row gets updated in a statement?

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2002-01-21 : 23:13:24

Hi NR,

The only reason I was using the COLUMNS_UPDATED was, that it was concise and very generic. But you are absolutely right. I did not think about the muliple updates. I am back to the update logic and I think I am going to come back tomorrow with some clear thinking. I am tired and hungry. But, NR thank you so very much. You have been fantastic!! Very very helpful~!!

Thanks sirre

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources