Delete trigger capturing text data

I am trying to use a trigger to capture text data that is being deleted and
put it into an audittrail table. I use the same logic for the insert
trigger(using inserted instead of deleted) and it functions as it should,
however when I do a delete, this trigger seems to be overlooked as no insert
ever occurs.
create trigger evidence_audit_delete on evidence
for delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowguid
= d.rowguid
endHi tracey,
If I understood it right, rowguid should be the key, so that row would be
deleted when you called the trigger. So the join returns no rows.
rewrite it this way.
create trigger evidence_audit_delete on evidence
for delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(d.note as
varchar(4000)),system_user,@.@.spid from deleted d
end
Let me know if this helps|||I put the join in because you cant seem to use the text data(even if its
converted into a varchar) from the deleted table. Is there a way to get the
trigger to fire before the delete maybe?
Server: Msg 311, Level 16, State 1, Procedure evidence_audit_delete, Line 7
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
tables.
"Omnibuzz" wrote:

> Hi tracey,
> If I understood it right, rowguid should be the key, so that row would be
> deleted when you called the trigger. So the join returns no rows.
> rewrite it this way.
> create trigger evidence_audit_delete on evidence
> for delete
> not for replication
> as
> begin
> insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name
,
> curr_val, username, session_id)
> select getdate(),'DELETE','evidence',d.rowguid,'note',cast(d.note as
> varchar(4000)),system_user,@.@.spid from deleted d
> end
> Let me know if this helps|||try this then (untested)
create trigger evidence_audit_delete on evidence
instead of delete
not for replication
as
begin
insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name,
curr_val, username, session_id)
select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowguid
= d.rowguid
delete from a from evidence a, deleted d where a.rowguid = d.rowguid
end|||That works
"Omnibuzz" wrote:

> try this then (untested)
>
> create trigger evidence_audit_delete on evidence
> instead of delete
> not for replication
> as
> begin
> insert dbo.audittrail (mod_date, upd_type, tbl_name, rec_primkey, col_name
,
> curr_val, username, session_id)
> select getdate(),'DELETE','evidence',d.rowguid,'note',cast(r.note as
> varchar(4000)),system_user,@.@.spid from deleted d, evidence r where r.rowgu
id
> = d.rowguid
> delete from a from evidence a, deleted d where a.rowguid = d.rowguid
> end|||good. If you are looking no further can you close the thread.
Thanks
Omnibuzz
"Tracey" wrote:
> That works
> "Omnibuzz" wrote:
>