All Forums Database
turgs 3 posts Joined 08/10
21 Feb 2011
Retrieving metadata about most recent query

Hello

In my stored procedures, I'm looking to start recording an "audit log" after each step.

So for example:

/*
* Step 1 in my Stored Proc
*/

DELETE FROM MyDatabase.MyTable_Step1 ALL;

INSERT INTO MyDatabase.MyTable_Step1
SELECT blah;

/*
* Insert Audit Log for the above step
*/

INSERT INTO MyDatabase.AuditLog(SPName, Step, Timestmp, RowsImpacted)
VALUES('MySP', 'Step 1', CURRENT_TIMESTAMMP, CountRowsImpacted() );

/*
* Step 2 in my Stored Proc
*/

UPDATE MyDatabase.MyTable_Step1
FROM ( SELECT 'blah' AS blah ) AS a
SET Col = a.blah
WHERE Age < 30;

/*
* Insert Audit Log for the above step
*/

INSERT INTO MyDatabase.AuditLog(SPName, Step, Timestmp, RowsImpacted)
VALUES('MySP', 'Step 2', CURRENT_TIMESTAMMP, CountRowsImpacted() );

The big I'm interested in is where I've written CountRowsImpacted() above. Obviously I've made that up and it's not a built-in Teradata function, but it explains what I'm looking to do.

Does anyone have any suggestions about how to achieve this result?

Kind regards
Tim

DGiabbai 47 posts Joined 07/04
24 Feb 2011

Use the "ACTIVITY_COUNT" status variable after a DML statement.

Pay attention that it is an INTEGER typed variable, limited to values up to 2^32-1

You must sign in to leave a comment.