Results 1 to 12 of 12

Thread: trigger

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: trigger

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Do they have to create a procedure and call it like this example:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    or ask us about what you are trying to do, rather than vague questions.
    Dave

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

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

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    If they still want to do their way, can you suggest how to accomplish what they're asking?

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by BELLO4KA View Post
    If they still want to do their way, can you suggest how to accomplish what they're asking?
    You could try generating an XML fragment, e.g. using XMLROW(), then storing it as XML type or converting to a string or CLOB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •