Basic Trigger Question 1

FatherRon · Nov 8, 2002

I've used triggers before in another DB where the total functionality was included in the CREATE TRIGGER statement, totally written in SQL. I understand that postgresql can't do that (correct?).

I want to delete a row in table 2 when I update table 1, with something along the lines of :
CREATE TRIGGER mytrigger AFTER UPDATE ON table1
FOR EACH ROW
DELETE FROM table2
WHERE table1.id = table2.id

Afterwards I will re-insert any necessary info into table 2.

Can anyone help me with this?

TIA

ps If I need to use a function to do this, pls show me how to do the same with a function.

rycamor · Nov 8, 2002

In PostgreSQL, you have two methods to affect a query upon execution: triggers and rules. With rules, you don't need to rely on an external function, as you do with triggers.

Technically, what a RULE does is to rewrite the incoming SQL statement to either replace the incoming statement, or to add an additional query to the statement. In some ways, this is much more elegant and simple than triggers. (but triggers can accomplish more complex tasks, since they use functions, which have access to procedural languages).

To handle your above need with a RULE, you would just do something like:

Code:

CREATE RULE myrule AS ON UPDATE
TO table1
DO (
          DELETE FROM table2
   WHERE table1.id = table2.id;
)

See more info on rules:

http://www.postgresql.org/idocs/index.php?rules.html

http://www.ca.postgresql.org/docs/aw_pgsql_book/node124.html

Here is some more information on triggers in PostgreSQL, if you want to learn more about them. Really, if you want to learn how to do triggers well in PostgreSQL, you should first learn how to use PostgreSQL procedural languages, especially Pl/PgSQL:

http://www.postgresql.org/idocs/index.php?sql-createtrigger.html

http://www.postgresql.org/idocs/index.php?triggers.html

http://www.ca.postgresql.org/docs/aw_pgsql_book/node166.html

-------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

rycamor · Nov 9, 2002

One small correction-- of course, you need to put a semicolon after the whole rule definition:

Code:

CREATE RULE myrule AS ON UPDATE
TO table1
DO (
          DELETE FROM table2
   WHERE table1.id = table2.id;
);

By the way, be aware that you can do multiple queries inside one rule, by separating them with semicolons:

Code:

CREATE RULE myrule AS ON UPDATE
TO table1
DO (
   DELETE FROM table2
     WHERE table1.id = table2.id;
   INSERT INTO log_table values(
     NEW.id,
     NEW.column2,
     NEW.column3
   );
);

-------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Basic Trigger Question 1

FatherRon

Programmer

rycamor

Programmer

rycamor

Programmer

Similar threads

Part and Inventory Search

Sponsor