CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
Antonio Antolcic
Antonio Antolcic

Reputation: 47

How does returned trigger from function affects BEFORE or AFTER statement?

I'm having a little trouble with understanding functions and triggers in sql. I didn't post the code of procedure chkInsertAritcle but let's say it returns NEW if it managed to make change and NULL if it didn't.

So my question is about the trigger. If I put AFTER INSERT does that means that it will complete INSERT without depending on the return value? And what happens with the rest of the rows?

Next question is if I put BEFORE INSERT, in what order does code runs?

Thanks!

CREATE TRIGGER ArticleIns
AFTER INSERT ON ListOfArticles
FOR EACH ROW
EXECUTE PROCEDURE chkInsertArticle();

Upvotes: 0

Views: 41

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246653

First all BEFORE triggers run in alphabetical order, then the operation is performed, then all AFTER triggers run in alphabetical order.

Each operation sees the result of the previous one as input, and if any trigger returns NULL, processing for that row stops. So if a BEFORE trigger returns NULL, the DML operation won't take place.

This happens independently for each row affected by the triggering DML statement.

Upvotes: 2

iainc
iainc

Reputation: 868

So if the trigger runs before insert, then the code runs before the data is inserted into the row and constraints are checked. So for example you might want to add a timestamp before the data is committed to the database,

If it runs after then the data is already present in the table and all constraints have been checked. This is usually where you want to trigger another process based on the row data, maybe update another table, send an e-mail etc.

In your example, the data will be in the database before your procedure runs. So if your procedure modifies the row data, it needs to be in the database.

Upvotes: 1

Related Questions