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

    Join Date
    May 2003
    Posts
    1
    Rep Power
    0

    Help with Triggers


    Hi guys, really hope you'll can help me with this one........
    I'm having a lot of trouble with triggers in PostGres,

    I had written the below trigger in Oracle :-

    CREATE OR REPLACE TRIGGER PERIPHERALS_AFT_UPD AFTER UPDATE ON PERIPHERALS FOR EACH ROW
    DECLARE
    EMP_NAME_OLD VARCHAR2(50);
    EMP_NAME_NEW VARCHAR2(50);
    BEGIN
    -- CHECKING FOR CHANGE OF EMPLOYEE
    IF :OLD.EMPID <> :NEW.EMPID AND :OLD.EMPID IS NOT NULL THEN

    SELECT NAME INTO EMP_NAME_OLD FROM EMPLOYEE
    WHERE EMPID=:OLD.EMPID;

    SELECT NAME INTO EMP_NAME_NEW FROM EMPLOYEE
    WHERE EMPID= :NEW.EMPID;

    --ADD EMPLOYEE CHANGED ENTRY TO PERIPHERAL ACTIVITY

    INSERT INTO PERIPHERALACTIVITY
    (PACTID, ACTIVITYDATE, DESCRIPTION, CATEGORY, PDID)
    VALUES
    (PACTID_SEQ.NEXTVAL, SYSDATE,
    'Employee changed from ' || EMP_NAME_OLD || ' to ' || EMP_NAME_NEW, 'EmpChg', :new.PDID);

    END IF;
    END;

    I need to rewrite the same trigger in PostGres Sql, cant figure out how to get it done. I am a newbie with pgsql, did read a bit of the documentation but it didnt help me too much..
  2. #2
  3. 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
    In postgres, triggers do not contain the function, but only call functions. The procedure is mapped out in the manual.

IMN logo majestic logo threadwatch logo seochat tools logo