#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    69
    Rep Power
    11

    Trigger indicator


    Hi

    I would like to know if an trigger is able to indicate whether an Update/Delete has occured in a table. I know this sounds very ironic but the reason is that I would like to insert records into a history table and indicate with the characters D-for delete and U-for update for the type of modification done for the specific record.

    I know it's possible to use 2 different triggers on update/on delete instead of using them together but then I am not sure whether to put D or U into the modification type field of the history table.

    If anyone has any other ideas I will gladly appreciate them as to how I will tackle this solution.

    tx
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    make a trigger for ON DELETE on the table for which you care about the modifications, and in the trigger function, INSERT into the modification_type field a 'D'

    make another trigger ON UPDATE on the table for which you care about the modifications, and in the trigger function, INSERT into the modificaiton_type field a 'U'.

    Now I don't know if you want to record EACH modification or the LAST modification, but if you want EACH then you just put a primary key on that table, select the current MAX primary key, increment it, and then make your compelte INSERT statement based on this primary key. Did I answer your question or not really?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    69
    Rep Power
    11
    Sort of but not really.

    Well I understand the solution, but why write it twice when it's going to do exactly the same thing except change the modification indicator to 'U' or 'D' depending on the event. I just thought that although you would say ON UPDATE OR DELETE, the trigger itself would know explicitly that's it's an delete or update is happening hence will be able to send the required argument to the call function.

    It would be really cool if this were possible.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You can call the same trigger procedure for both. E.g in the top level block of a pl/pgsql trigger function the variable TG_OP will be set with a value of INSERT, UPDATE, or DELETE reflecting on the operation for which the trigger was fired.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    69
    Rep Power
    11
    Firstly, thanx to you guys who have assisted me!

    Are there any sites that you can recommend that will assist me as to creation of triggers, also when I create a trigger and call a procedure, I get the error that the function does not exist, do you know why I get such an error.

    tx
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    I've had the same sort of problem before, it usually depends on if you create the trigger first or the function first. Somehow you can confuse the backend about which function you're trying to use; I think it assigns an OID to the function when you create it; so if you reference a function from a trigger, it tries to look it up by OID. It may be possible that you need to just drop and then recreate your function. Or, are there any errors when you create the function, or only the trigger?

    websites for triggers :



    the plpgsql triggers ref. in the postgres documentation

    AND the O'Reilly book Practical Postgresql:



    stuff about plpgsql and triggers

IMN logo majestic logo threadwatch logo seochat tools logo