How to update audit columns in a table using triggers?

UDA

How to update audit columns in a table using triggers?

Hi,

I have a Teradata table with audit columns like modified_date and modified_by. I would like to write a trigger in Teradata so that when a row is updated in this table the modified_date and modified_by columns get the current_timestamp and user values. Since the SELECT clause can only be used in the WHEN part of the trigger, I do not know how to set these column values :-(

Has someone tried this before? Are there any other ways to do this?

Thank You,
Kranthi
2 REPLIES
Enthusiast

Re: How to update audit columns in a table using triggers?

Yes, you can do this in two parts:

1) Create an update statement trigger (and insert statement trigger if you want this to happen on inserts) to update the modified_date and modified_by columns when any of the other columns are updated in the table. Do not include the "modified_date" or "modified_by" columns in the WHEN clause.

2) Prevent the user from directly updating the modified_date and modified_by columns by either applying column level security or making them update through a view that excludes these columns.

The reason for number 2 is that you only want those columns updated through the trigger, not by the user directly.

Enthusiast

Re: How to update audit columns in a table using triggers?

I've been working on a similar trigger to manage my user managed dimensions.
What I really want is an 'Instead of' trigger which was depreciated in v2.5?
so I've had to use an AFTER UPDATE instead.

The objective to allow users to UPDATE the exisiting row, but then have the trigger intercept the UPDATE and translate into an INSERT with the new values and mark as current, then update the previous row, leaving the original values intact and expire the record.

[font=Courier New]
REPLACE TRIGGER sandbox.Dim_MetricProfile_AU_tg
AFTER UPDATE OF
MetricProfileDeviceType
,MetricProfileMessageType
ON sandbox.Dim_MetricProfile order 2
REFERENCING OLD ROW AS Orow
NEW ROW AS Nrow
for each row when (nrow.RowIsActiveFlag='Y')
(
-- copy current row values as for new dimension row
INSERT into sandbox.Dim_MetricProfile(
MetricProfileDeviceType
,MetricProfileMessageType
,RowIsActiveFlag
,RowExpiredTimestamp
,RowEffectiveTimestamp)
values (nrow.MetricProfileDeviceType
,nrow.MetricProfileMessageType
,'Y', NULL,current_timestamp(0)) ;
-- update the prior row to the original values
Update sandbox.Dim_MetricProfile
set MetricProfileDeviceType = orow.MetricProfileDeviceType
, MetricProfileMessageType = orow.MetricProfileMessageType
, RowExpiredTimestamp = current_timestamp(0)
, RowIsActiveFlag = 'N'
where MetricProfileID = orow.MetricProfileID;
);
delete from sandbox.Dim_MetricProfile;
INSERT into sandbox.Dim_MetricProfile( MetricProfileDeviceType ,MetricProfileMessageType)
values ('dev','msg');
select * from sandbox.Dim_MetricProfile;
UPDATE sandbox.Dim_MetricProfile set MetricProfileDeviceType='dev7' where RowIsActiveFlag='Y';
select * from sandbox.Dim_MetricProfile;

CREATE SET TABLE Sandbox.Dim_MetricProfile ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
MetricProfileID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE -2147483647
MAXVALUE 2147483647
CYCLE),
MetricProfileMessageType VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
MetricProfileDeviceType VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
RowIsActiveFlag CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL DEFAULT 'Y' COMPRESS ('N','Y'),
RowEffectiveTimestamp TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
RowExpiredTimestamp TIMESTAMP(0) DEFAULT NULL COMPRESS ,
RowDataSource CHAR(4) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL DEFAULT 'dcl ' COMPRESS 'dcl ',
RowAuditBatchID INTEGER NOT NULL DEFAULT 1 COMPRESS 1 )
UNIQUE PRIMARY INDEX XPKDim_OpMetricProfile ( MetricProfileID );

[/font]