Postgres triggers

Postgres trigger
Database triggers are server side function that are executed on a row or statement when a DML command is received to the server. It's a good way to "fool" applications and users to believe things are done as they should or simply to cascade an command to more tables, i.e. logging.
Today (yes, on a damn Saturday) I was asked to try to create a scenario to stop our application from changing a value in the database so our client unit would repeat an operation for regression testing purpose (without modifying application code).
Simple, just add a trigger to change it back.

Here is a small trigger example for PG 8 (almost identical in Oracle except for some syntax changes):

-- Create sample table
CREATE TABLE dogs (
 DOG_ID  SERIAL PRIMARY KEY,
 NAME    VARCHAR(20),
 STATUS  VARCHAR(1)
);

-- Create function
CREATE FUNCTION dogstat() RETURNS trigger AS '
BEGIN
IF NEW.name=''FIDO'' THEN
  IF NEW.status=''S'' THEN
    UPDATE dogs SET STATUS=''A'' where name=''FIDO'';
  END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER dogtrigg AFTER UPDATE ON dogs
  FOR EACH ROW EXECUTE PROCEDURE dogstat();

-- Insert test
INSERT INTO dogs(name,status) VALUES('FIDO','A');
INSERT INTO dogs(name,status) VALUES('PLUTO','A');
-- Ok lets test
test1=> select * from dogs;
 dog_id | name  | status
--------+-------+--------
      1 | FIDO  | A
      2 | PLUTO | A
(2 rows)

test1=> update dogs set status='S' where name='FIDO';
UPDATE 1
-- Ok what happened?
test1=> select status from dogs where name='FIDO';
 status
--------
 A
(1 row)
-- Nothing really! Noone can (S)ell FIDO! :-)
-- What about PLUTO?
test1=> update dogs set status='S' where name='PLUTO';
UPDATE 1
test1=> select status from dogs where name='PLUTO';
 status
--------
 S
(1 row)
-- SOLD!

Ok, not to difficult.
However, I do not advice to use triggers all over the place in production databases. This sort of logic should be at the application level in my opinion, but in this case for a regression test scenario it works very well.

Want to read more?
Postgres 8.0 trigger docs