×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Basic Trigger Question

Basic Trigger Question

Basic Trigger Question

(OP)
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.

RE: Basic Trigger Question

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:

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)

RE: Basic Trigger Question

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

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:

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close