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

SQL Server Development (2000)

Triggers..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-06 : 08:33:30

Hi All,

I have an Update Trigger, is there any way i can get which column in the table is updated?

i know we can get through If Update(ColumnName) .... but this is not feasable for me...

i want to maintain the history table. so if some column is updated then i have to update the same column in the history table. where as let all the other records be null or default..

now there are around 235 columns in the master table so i dont want to put 235 if conditions..

if you have any better solution for this then please let me know?

Thanks in Advance

Complicated things can be done by simple thinking

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-06 : 09:01:34

You have to code the 235 conditions.
To generate the code see
http://www.mindsdoor.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html
http://www.mindsdoor.net/SQLTriggers/GenerateTriggerForAudit.html
http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TimS
Posting Yak Master

198 Posts

Posted - 2005-08-06 : 16:17:48

How about this for update only trigger. Depending on your logic may want it to be NULLIF(d.ColA, i.ColA) instead of NULLIF(i.ColA, d.ColA) etc.

INSERT INTO history(ColA, ColB)
SELECT NULLIF(i.ColA, d.ColA) AS ColA, NULLIF(i.ColB, d.ColB) AS ColB
FROM inserted i
JOIN deleted d ON i.PK = d.PK

The above was just written & no testing done.

Tim S

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-06 : 16:39:38

will fasil if the new value is null.
Have a look at the links I posted.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TimS
Posting Yak Master

198 Posts

Posted - 2005-08-06 : 19:13:41

Here's what I thought he wanted; it is not what I do; but I am maintaining one or two history tables using this type of trigger.
Tim S

SET NOCOUNT ON;

-- Test table to be audit trailed.
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null, j_int_key int not null, s_varchar varchar(10), t_char varchar(10), d_date datetime)
go
alter table trigtest add constraint pk primary key (i_int_key, j_int_key)
go

-- Audit trail table
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest_au]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest_au]
go
create table trigtest_au (i_int_key int not null, j_int_key int not null,
old_s_varchar varchar(10), new_s_varchar varchar(10),
old_t_char varchar(10), new_t_char varchar(10),
old_d_date datetime, new_d_date datetime,
UpdateDate datetime, UserName varchar(128), type varchar(1))
go

-- Trigger to save old and new values of fields that have changed

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_au_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_au_trigtest]
go
create trigger tr_au_trigtest on trigtest for insert, update, delete
as
declare @type varchar(1) ,
@UpdateDate datetime ,
@UserName varchar(128)
if exists (select * from inserted) and exists (select * from deleted)
select @type = 'U'
else if exists (select * from inserted)
select @type = 'I'
else
select @type = 'D'

select @UpdateDate = getdate() , @UserName = system_user

IF @type = 'I'
INSERT INTO trigtest_au (i_int_key, j_int_key, new_s_varchar, new_t_char, new_d_date, UpdateDate, UserName, type)
select i.i_int_key, i.j_int_key, i.s_varchar, i.t_char, i.d_date, @UpdateDate, @UserName, @type
from inserted i

ELSE

INSERT INTO trigtest_au (i_int_key, j_int_key,
old_s_varchar, new_s_varchar,
old_t_char, new_t_char,
old_d_date, new_d_date,
UpdateDate, UserName, type)
select COALESCE(i.i_int_key, d.i_int_key) , COALESCE(i.j_int_key,d.j_int_key),
NULLIF(d.s_varchar, i.s_varchar), NULLIF(i.s_varchar, d.s_varchar),
NULLIF(d.t_char, i.t_char), NULLIF(i.t_char, d.t_char),
NULLIF(d.d_date, i.d_date), NULLIF(i.d_date, d.d_date),
@UpdateDate, @UserName, @type
from inserted i
full outer join deleted d
on i.i_int_key = d.i_int_key and i.j_int_key = d.j_int_key

go

-- Test updates
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 1, 1, 'hello', 'goodbye', '20000101'
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 2, 1, 'hello', 'goodbye', '20000101'
update trigtest set s_varchar = 'helloupd' where i_int_key = 1
update trigtest set t_char = 'goodbyeupd', d_date = '20000102' where i_int_key = 1
update trigtest set t_char = null, d_date = null where i_int_key = 1
update trigtest set t_char = 'good', d_date = '20000103' where i_int_key = 1
delete trigtest where i_int_key = 1

GO

--
select i_int_key, j_int_key, old_s_varchar, new_s_varchar, old_t_char, new_t_char, old_d_date, new_d_date, UpdateDate, UserName, type
from trigtest_au
ORDER BY UpdateDate ASC

Edit: Most of my history I am maintaining have only the new_ columns or the old_ columns not both.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-06 : 21:14:51

The links I gave show how to generate something similar from the table structures or to log column changes with a generic trigger for all tables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 02:59:31

--hello Chirag,
--check the scrip of trigger and see if it fullfills ur requirement or not
-----

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRIGTEMP1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRIGTEMP1]
GO

CREATE TRIGGER [TRIGTEMP1] ON [dbo].[employee]
FOR UPDATE
AS
declare @a int, @b int, @C int, @d varchar(200), @E int
SELECT @c = COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE'
set @a = 1
while @a < @c + 1
BEGIN
set @b = convert(int,SUBSTRING(COLUMNS_UPDATED(),@a,1))
IF @b > 1
begin
if (@b & 1 = 1)
begin
SET @e = (@a - 1) * 8 + 1

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 2 = 2)
begin
SET @e = (@a - 1) * 8 + 2

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 4 = 4)
begin
SET @e = (@a - 1) * 8 + 3

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 8 = 8)
begin
SET @e = (@a - 1) * 8 + 4

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 16 = 16)
begin
SET @e = (@a - 1) * 8 + 5

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 32 = 32)
begin
SET @e = (@a - 1) * 8 + 6

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 64 = 64)
begin
SET @e = (@a - 1) * 8 + 7

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end

if (@b & 128 = 128)
begin
SET @e = (@a - 1) * 8 + 8

SELECT @d = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMPLOYEE' and ORDINAL_POSITION = @e
PRINT @d
end
end

set @a = @a + 1
end

Kapil Arya

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-14 : 20:41:15

It might do but why duplicate all that code.
Have a look at the links I gave to see how to loop through the columns.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources