PostgreSQL | Trigger Usage Example

Tags:

1. Create a Dummy Table

CREATE TABLE tb_account(uid INT, id TEXT, pw TEXT, register date);

2. Declare the UpdateRegister Function

CREATE FUNCTION UpdateRegister() RETURNS OPAQUE AS
$$
DECLARE
BEGIN
    UPDATE tb_account SET register = now() WHERE uid = new.uid;
    RETURN NULL;
END
$$ LANGUAGE 'plpgsql';

3. Declare the UpdateRegisterTrigger Trigger

CREATE TRIGGER UpdateRegisterTrigger
AFTER INSERT on tb_account
FOR EACH ROW EXECUTE PROCEDURE UpdateRegister();

4. Use the Trigger

INSERT INTO tb_account(uid, id, pw) VALUES (1, 'james', '007');
INSERT INTO tb_account(uid, id, pw) VALUES (2, 'tomas', '008');
INSERT INTO tb_account(uid, id, pw) VALUES (3, 'suzan', '008');

5. Check the Result

SELECT * FROM tb_account;