Learning new things should be fun. I’ve read about database triggers multiple times but I didn’t have a chance to use them. Triggers are not popular these days especially in web development. Let’s mix learning and making fun (of others).
What is a trigger for? It’s a kind of aspect of programming, an extra business logic executed on a given action. With triggers you can do complex validation, update balance on summary tables, record the who, what and when of changes to data and so on. That’s triggers in a nutshell.
Suppose your buddy works on yet another CRUD. She/he is a hardcode server side developer and she/he is probably not familiar with RDBMS and triggers at all. Let’s do something a bit evil.
She/he is working on a new data entry form for table
user_account. Let’s break the app in a fancy way. You’ll have to have access to their database for this to work. During a development phase it is usually not a problem to get access to a development database. Just login to her/his database and execute the following queries:
CREATE OR REPLACE FUNCTION prank1() RETURNS TRIGGER AS $prank1$ BEGIN delete from user_account where id = NEW.id; return null; END; $prank1$ LANGUAGE plpgsql;
CREATE TRIGGER prank1 after INSERT ON user_account FOR EACH ROW EXECUTE PROCEDURE prank1();
If you don’t understand how these queries work, just wait for ... hmmm… cursing. This trigger converts the
user_account table into a black hole. Every row inserted into it is immediately deleted. Hah! Put a poker face mask on and wait for your friend’s resolution.
Suppose she/he survived your cruelty. Let’s do something more subtle. It’s easy to make a mistake during development, for example to store a field value in the wrong column. I try to store values in the
first_name field in the
last_name column sometimes; relying on method completion in modern IDEs is a double edged sword.
Execute these queries:
CREATE OR REPLACE FUNCTION prank2() RETURNS TRIGGER AS $prank2$ DECLARE tmp varchar(255); BEGIN tmp := NEW.first_name; NEW.first_name := NEW.last_name; NEW.last_name := tmp; return NEW; END; $prank2$ LANGUAGE plpgsql; CREATE TRIGGER prank2 before UPDATE ON user_account FOR EACH ROW EXECUTE PROCEDURE prank2();
This trigger swaps first name and last name on every row update. You’ve got to run really fast when your buddy discovers it.
I’ve got a few more ideas but I’ll keep them to myself, for security reasons ;) As you can see, triggers can be funny but they can be really useful, too. Especially if you want to keep the database consistent while having multiple applications connected to it.
Disclaimer: No Developers Were Harmed During The Making of This Article.