Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: create a new trigger

    Hi,
    I've 2 tables (A, B)

    A has this columns
    cod varchar2(32) primary key
    admin varchar2(32)

    B has
    cod varchar2(32)
    cod_admin varchar2(32)

    I'd to create a trigger that when I update A.admin with value 'X', in tab B I get B.cod=A.cod and B.cod_admin='999'.
    This also for insert and delete.

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: create a new trigger

    Originally posted by raf
    Hi,
    I've 2 tables (A, B)

    A has this columns
    cod varchar2(32) primary key
    admin varchar2(32)

    B has
    cod varchar2(32)
    cod_admin varchar2(32)

    I'd to create a trigger that when I update A.admin with value 'X', in tab B I get B.cod=A.cod and B.cod_admin='999'.
    This also for insert and delete.

    Thanks
    Raf
    I can't follow that requirement exactly, but you would write the triggers something like this:

    CREATE OR REPLACE TRIGGER trigger_name
    AFTER UPDATE OF admin ON A
    FOR EACH ROW
    WHEN (NEW.admin = 'X')
    BEGIN
    INSERT INTO B (cod,cod_admin) VALUES (:NEW.cod,'999');
    END;
    /

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    ok....but if I want delete this value or if I write
    update A
    set admin=null
    where cod=cod;

    ??

    Raf

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, I don't know what you want to happen in those cases. But you can either create a trigger for each event, or combine several events into one trigger, e.g.

    CREATE TRIGGER trig
    AFTER INSERT
    OR UPDATE OF admin
    OR DELETE
    ON A
    FOR EACH ROW
    BEGIN
    ...
    END;
    /

    Within the trigger, you can have different logic for inserts, updates and deletes if required like this:

    IF INSERTING THEN
    ...
    ELSIF UPDATING OR DELETING THEN
    ...
    END IF;

    And within the trigger you can look at the "before" and "after" values of the triggering row using :OLD and :NEW, like:

    IF :OLD.admin != :NEW.admin THEN
    ...
    END IF;

    (beware of NULLs - :OLD values are all NULL for inserts, and :NEW values are all NULL for deletes).

    As for what the logic of YOUR trigger should look like, I could not possibly know that! I suggest you have a go at it yourself, using the above as a guide, and then come back here if you hit any specific problems.

Posting Permissions

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