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:
>

delete output into <xml column of audit table>

Currently running Sql Server 2005
Is it possible to issue the delete command and capture the affected rows as
xml types that will be stored in an audit table with an xml column?
Something along the lines of:
delete from source_table
output
(deleted.*
into audit_table (xml_audit_column)
for xml auto)
where source_table.column = @.delete_valueYou cannot do it in a single statement because the OUTPUT clause does not
support subqueries. One way is to get the deleted rows into a temp table and
then transfer as XML to your audit table.
Here is one example of implementing this in a trigger:
http://blogs.sqlservercentral.com/prosqlxml/archive/2008/01/26/create-a-dynamic-logging-trigger-with-xml.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com