If Update() in trigger - 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)

If Update() in trigger

Author

Topic

sgtwilko
Starting Member

23 Posts

Posted - 2001-11-08 : 06:02:21

Hiya,
I've been trying to get

 if update()

to work when using a variable to pass the field name, but all I get is a incorrect syntax error.

What I'm trying to do is insert into a table a list of fields that were updated, and there old and new values (you can see where I'm heading below).

Yes, I'm using a cursor, I found more examples of them when I was learning this, and I've not had time to work out another way, yet.

Feel free to convert it to none-cursor if you so wish :-)

Any Help would be much appreciated


CREATE TRIGGER tr_tcd_journal ON [dbo].[t_ContractorDetails] 
for UPDATE
AS
BEGIN
	declare @numFields int, @colName nvarchar(255), @tableName nvarchar(40), @value nvarchar(4000), @uidcol varchar(255), @bol bit
	set @tableName='t_ContractorDetails'
	set @bol=0
	set @value='Bob'
	Declare cur_fields CURSOR 
		For Select Column_Name from INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = @tableName)
	Open cur_fields
	FETCH NEXT FROM cur_fields Into @ColName
	While (@@Fetch_Status = 0 ) and (@bol=0)
	Begin
		select @bol=COLUMNPROPERTY( OBJECT_ID(@tableName),@ColName,'IsIdentity')
		if @bol=1
		Begin
			set @uidcol=@ColName
		end
		FETCH NEXT FROM cur_fields Into @ColName
	end
	Close cur_fields
	open  cur_fields
	FETCH NEXT FROM cur_fields Into @ColName
	While (@@Fetch_Status = 0 )
	Begin
		IF UPDATE(ColName)
		begin
			INSERT INTO t_Journal
				(nv_Table, nv_Field,dt_DateTime, nv_OldValue, nv_NewValue)
				VALUES     (@tableName,@ColName, GETDATE(), @uidcol,@value)
		end
		FETCH NEXT FROM cur_fields Into @ColName
	end
	Close cur_fields
	Deallocate cur_fields
END

Wolle
Starting Member

1 Post

Posted - 2001-12-05 : 08:36:27

How can i get old and newvalue in tr_tcd_journal?
I tried so build a dynamic statement but it doesn't work.
Can anybody complete this trigger? (sorry i'm a t-sql newbie)
I tried following code:


...
			IF (substring(Columns_updated(),@eights+1,1) & power(2,@ordinal-(8*@eights)))=power(2,@ordinal-(8*@eights))
			Begin
				select @oldvalue = @colname from deleted where deleted.ID in(select ID from TestTable)
...

@oldvalue returns only the actual fieldname

then i tested:

set @strSQL = N'select '  + @colname + ' from deleted where deleted.ID in(select ID from TestTable)'
execute sp_executesql @strSQL, N'@Oldvalue varchar(4000) output', @Oldvalue output

an i get the error 'object deleted is unknown'
getting more and more confused

...Wolle
Edited by - wolle on 12/06/2001 04:56:36

Edited by - wolle on 12/06/2001 06:38:06

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources