instead of triggers

[ethorn10]

Hello all...

I have a question about INSTEAD OF triggers. I am trying to use them so that I can successfully audit ntext columns. However, I'm struggling with the ability to accurately capture the ID field (autoincrement PK field). I can handle the rest, it's just keeping this ID field in line is turning out to be a pain. I have done some initial digging but can't find anything that really addresses my problem, the only stuff I've found is a basic copy/paste of BOL for INSTEAD OF triggers (which mentions nothing about my issue).

Thanks in advance guys...

 

[ethorn10]

Ok. I think I may have solved this by myself for those who were curious (apparently not many). In the INSTEAD OF trigger, I insert into the main table first, use IDENT_CURRENT to get the autoincrement PK and then insert into the audit table with that value.

Let me know if this is heading down the wrong path.

Thanks.

 

[vongrunt]

Um... no.

How 'bout inserted/deleted pseudotables?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[ethorn10]

inserted/deleted pseudotables do not store autoincrement (or IDENTITY) values in them. i tried that already, it holds a NULL value. but thanks for the reply.

at least it didn't work for me that way.

 

[vongrunt]

> inserted/deleted pseudotables do not store autoincrement (or IDENTITY) values in them.

Hm... check this:

create table blah ( id int identity(1, 1) primary key, lastname varchar(30), firstname varchar(30))
go

create table blahaudit( eventDate datetime default getDate(), PKValue int, action char(1) )
go

create trigger blahtrigger on blah
for insert, update, delete
as
	insert into blahaudit (eventDate, PKValue, action)
	select getdate(),
		isnull(I.id, D.id), 
		case when I.id is null then 'D' when D.id is null then 'I' else 'U' end
	from inserted I
	full outer join deleted D on I.id = D.id
go

insert into blah (lastname, firstname) values ('Smith', 'Joe')
select * from blahaudit
update blah set firstname = 'Will' where id = 1
select * from blahaudit
delete from blah where id = 1
select * from blahaudit

drop table blah
drop table blahaudit

Another question was: why INSTEAD OF trigger?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[ethorn10]

yes your code would work beautifully (and i have several of those triggers in place already) but this particular case needs NTEXT to be audited also...thus the INSTEAD OF. sorry if that got lost in the fog on the first one.

 

[ethorn10]

I went ahead and used the solution I found above considering I was in need of auditing the NTEXT field as well.

However, now I am getting new problems. The triggers successfully insert and update, depending on the action taking place, and they also successfully capture the data in the audit table. But despite the data being saved, the front end never makes it back. I get an EXCEPTION_ACCESS_VIOLATION error (c0000005). I have commented out the ntext fields in the trigger in various combinations and can't figure out why I don't get the EAV error sometimes and not other times. Does anybody have any insight into this nightmare?

Thanks in advance...