Questions (not mine):
--------------------
I need some more information on triggers.
Is the user name performing an insert/update/delete available in an after trigger available and how is it obtained?
Is there a method to obtain the table that was accessed that fired the trigger?
--------------------


I think these questions are related to auditing... Is there anything you can specify when creating a trigger that can help answer some of them? Or any other method of getting this info?

1) the special register CURRENT USER
2) I do believe that when the trigger is created, it is on a specific table or view. If the triggered action needs to do something with it, the developer of the trigger can supply the table/view name for that action. It can also be obtained in the catalog (syscat.triggers).

Andy

Thanks, Andy.

Another question:
Is only SQL stmts allowed in a trigger or can IF-THEN-ELSE logic also be incorporated, and if so, how is it accomplished? They need to determine if the userid executing should be audited and data collected.

Yes, you can use IF-THEN-ELSE logic (along with WHILE, FOR, etc) . You can even use compound statements. Look at the manual for the complete syntax.

Andy

Do they have to create a procedure and call it like this example:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

That is one way to do it. But it might be able to be done without it. Like I said, read the manual for the complete syntax.

Andy

or ask us about what you are trying to do, rather than vague questions.
Dave

Another question please (not from me):

-----------------
Is there a way to get the before and after images into the audit table I created. Tried concatenating the columns to no avail…
Below is the table and trigger …

CREATE TABLE audit.audit_info
(timestamp timestamp not null,
userid varchar(10) NOT NULL,
table_name varchar(128) not null,
function varchar(6) not null,
image_type varchar(6) not null,
data_image varchar(4000) NOT NULL
)
IN audit_ts;


CREATE TRIGGER ai_test2
AFTER INSERT ON audit.test1
REFERENCING
NEW AS n
FOR EACH ROW
MODE DB2SQL
when ((select current user from sysibm.sysdummy1) in (select userid from audit.users))
INSERT INTO
audit.audit_info
values
(
current_timestamp,
(select current user from sysibm.sysdummy1),
'test1',
'insert',
'after',
'this is difficult' <= this is the problem area…
)


I tried to use n.c1||’,’||n.c2 which are the columns on the table but did not accept it.

------------------

Why not create the audit table specific to the table it is auditing like:

CREATE TABLE audit.audit_tab1_info
(timestamp timestamp not null,
userid varchar(10) NOT NULL,
old_col1 int,
new_col1 int,
old_col2 char(2),
new_col2 char(2),
)
IN audit_ts;

Then your trigger is trivial.

Andy

If they still want to do their way, can you suggest how to accomplish what they're asking?

Well if you want to concatenate all the columns together, you will need to convert the non-character columns to CHARACTER or VARCHAR first. If the data all concatenated together exceeds the length of the column you want to put it in, then it will get truncated and you will have data loss. This method will also require humans to determine what changed as it will not be easy to have SQL be able to figure it out.

Andy

You could try generating an XML fragment, e.g. using XMLROW(), then storing it as XML type or converting to a string or CLOB.