Saturday, September 3

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

3 comments:

billyk said...

Author:

"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."

Response:

This is probably the dumbest thing I have heard a person say regarding the role of the DB in the application development. Every DBA and software professional knows that the database is absolutely the correct place to contain business logic, including triggers.

Perhaps the author was referring to the problem that can occur when triggers are not well planned throughout the DB and you get a cascading effect that fires one trigger after another uncontrollably.

Couple of misspellings, too.

Anonymous said...

bilyk : If only you understood layer separatation...

amen said...

best postgres trigger example!
simple, precise & great advice!!!

btw, i subscribe to tha bus logic app layer camp ;)