Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    110

    Unanswered: trigger question

    Here goes:

    i have three tables, table1, table2, table3. They are al similar lets say:

    create table<1-3>
    (
    col1 int,
    col2 int);

    Now that i got 3 tables.. i want only use table 1 for inserts update and deletes.

    If i insert/update/ on table1 it will be triggerd to table2. Table3 will be used for archieving and should only be filled if a delete takes place.

    There are no trigger problems when i insert or update on table1. The trigger to table2 works perfectly but if i delete a row on table1 it should be deleted on table2 but inserted on table3.

    the function on table1 looks like this:

    CREATE FUNCTION table1() RETURNS OPAQUE as '
    BEGIN
    INSERT INTO table2
    values
    (
    NEW.col1,
    NEW.col2
    );
    RETURN NULL;

    END;
    ' language 'plpgsql';

    and the trigger:

    CREATE TRIGGER table1
    AFTER INSERT or UPDATE
    ON table1
    FOR EACH ROW
    EXECUTE PROCEDURE table1();

    But i cant figure out how the function would look like if i wanted to perform a triggerd delelete on table2 from table1 wich would result in a inserted trigger to table3.

    Perphaps its not even possible.. but it makes me head spin not knowing....

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    roles

    maybe you try it with rules

    Create rule

    so you cann define a rule on each of the three tables
    and write

    INSTEAD OF
    INSERT
    DO INSERT
    INSERT
    INSERT

    so you have not a problem with triggers
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Mar 2004
    Posts
    110
    Thanks for the idea.

    I'll give it a shot.

    -Ed

  4. #4
    Join Date
    Mar 2004
    Posts
    110
    Well, i've managed to create what i was looking for (all updates/insert/deletes) on one table that will "copied" to another active table and upon delete will be copied to a "archive" table and clearing the other two tables.

    If anyone is interested in the code..let me know an ill post it.

    Thanks for pointing me into the right direction (rules).

    -Ed

  5. #5
    Join Date
    Jun 2004
    Posts
    57

    Rules instead of instead of triggers

    If I understand correctly or if I not I would appreciate if you can advice me in the problem of using rules instead of "instead of insert/update/delete" on views.
    If I have, for example three tables:
    A (id, name)
    B(fk1, name)
    C(fk2, name)
    And a view named V
    create view V as
    select * from A,B,C where A.id = B.fk1 and B.fk1 = C.fk2)

    I would like to insert, update and delete in these three tables using the view. I cannot use instead of triggers because they are replaced with rules. But I don't know what is my mistake because I am informed that "ERROR: NEW used in query that is not in a rule" when I create the rule like that:

    CREATE or replace RULE r AS ON INSERT TO v DO INSTEAD
    INSERT INTO A (id, name) VALUES (NEW.id, NEW.name);
    INSERT INTO B (fk1, name) VALUES (NEW.fk1, NEW.name);
    INSERT INTO C (id, name) VALUES (NEW.fk2, NEW.name);

    Can anyone tell me, please, in this example, how can I substitute an instead of view with a rule?
    Thanks in advanced.

Posting Permissions

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