Trigger to prevent update in postgresql

Question

I want to create a trigger which will prevent update in a row from that table if that entry is present in another table too.

example

Table M
m_id   title  
1       abc
2       def
3       ghi

Table N
n_id   m_id
1       2
2       3

so if try to update values 2 or 3 in table m it shouldn't allow that whereas if i try to update value 1 from table it should do so.

Laurenz Albe · Accepted Answer · 2017-06-30 07:50:45Z

4

You'll have to create a trigger BEFORE UPDATE ON m FOR EACH ROW that checks IF OLD.m_id <> NEW.m_id AND EXISTS (SELECT 1 FROM n WHERE n.m_id = OLD.m_id) and throws an exception in that case.

edited Jun 30, 2017 at 7:50

answered Jun 23, 2017 at 11:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bhawesh Chandola Over a year ago

Sorry for late reply but i already have a foreign key constraint what i want is that if i try to update the entry from table M which is present in table N then shouldn't allow me to do so.

Bhawesh Chandola Over a year ago

can you give the final query if possible?

Collectives™ on Stack Overflow

Trigger to prevent update in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related