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).
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.
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
CREATE TRIGGER ai_test2
AFTER INSERT ON audit.test1
NEW AS n
FOR EACH ROW
when ((select current user from sysibm.sysdummy1) in (select userid from audit.users))
(select current user from sysibm.sysdummy1),
'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.
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.