|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-26 : 17:23:53
|
Edmund writes "The database tables that I am working on currently require an audit trail. For exmaple, for every table_x there is a table_x_aud.
table_x has two triggers, one for update and one for insert. Essentially they server the purpose of creating an audit trail. for each insert or update, the specific record in table_x will be copied into table_x_aud with a datestamp, timestamp and an operator id.
table_x has several columns, a few being of type 'text'. The triggers however return an error 311 - Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Is there any other way to create a trigger to perform this function?
---------- trigger script ------------
create trigger trig_ins_table_x_tbl
on table_x_tbl
for INSERT as
begin
declare
@now_stamp datetime,
@DEF_INIT_REV_NO int,
@tm MPTIME
if @@rowcount = 0
return
select @now_stamp = convert(char(8),getdate(),112)
select @DEF_INIT_REV_NO = 0
select @tm = datediff(second,@now_stamp,getdate())
declare @table_x_id RECNUM
select @table_x_id = breach_id from inserted
/* update breaches_tbl with datestamp and timestamp values */
update table_x_tbl set
revision_no = @DEF_INIT_REV_NO, time_stamp = @tm, date_stamp = @now_stamp
where table_x_id = @table_x_id
if @@rowcount != 1
raiserror ('table_x_tbl Update Trigger Failed: 1',16,1) with seterror
/* insert for table_x_aud */
insert into table_x_aud (
table_x_id, pmf_id, breach_date, breach_nature_id, action_taken,
further_action, prevent_action, breach_status_id, date_resolved, breach_type_id, status_id, entry_date,
revision_no, operator, date_stamp, time_stamp)
select
table_x_id, pmf_id, breach_date, breach_nature_id, action_taken,
further_action, prevent_action, breach_status_id, date_resolved, breach_type_id, status_id, entry_date,
@DEF_INIT_REV_NO, operator, @now_stamp, @tm from inserted
if @@rowcount != 1
raiserror ('table_x_tbl Insert Trigger Failed: 2,16,1) with seterror
end
--------------- end of script ---------------
action_taken, further_action and prevent_action are TEXT columns.
Rgds,
Edmund" |
|
text column needed in trigger