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 tableOk, not to difficult.
CREATE TABLE dogs (
DOG_ID SERIAL PRIMARY KEY,
-- Create function
CREATE FUNCTION dogstat() RETURNS trigger AS '
IF NEW.name=''FIDO'' THEN
IF NEW.status=''S'' THEN
UPDATE dogs SET STATUS=''A'' where name=''FIDO'';
' 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
test1=> update dogs set status='S' where name='FIDO';
-- Ok what happened?
test1=> select status from dogs where name='FIDO';
-- Nothing really! Noone can (S)ell FIDO! :-)
-- What about PLUTO?
test1=> update dogs set status='S' where name='PLUTO';
test1=> select status from dogs where name='PLUTO';
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