Contoh Trigger Procedure Postgres, PostgreSQL

Some syntax can be use in trigger is :

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETEoperations.

---

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.

---

TG_NAME

Data type name; variable that contains the name of the trigger actually fired.

---

TG_WHEN

• Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.

---

TG_LEVEL

Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

---

TG_OP

Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

---

TG_RELID

Data type oid; the object ID of the table that caused the trigger invocation.

---

TG_RELNAME

Data type name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.

---

TG_TABLE_NAME

Data type name; the name of the table that caused the trigger invocation.

---

TG_TABLE_SCHEMA

Data type name; the name of the schema of the table that caused the trigger invocation.

---

TG_NARGS

Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.

---

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

---

Example of function (Store Procedure) to will be execute by trigger :

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

And then create a trigger to execute function above:

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Hope this example can be help you. for detail information you can visit to postgres doc

Categories: Databases