Solved

trigger lang SQL?

Posted on 2004-10-28
2,766 Views
Last Modified: 2008-02-01
Hi Folks,

I'm needing to update rows in one table based on updates to another.  the obvious solution is a simple sql update fired by trigger.

My reading of the postgresql docs suggests that sql function is not supported by triggers?

what would be the second best approach?  any samples/examples anywhere?

Thanks & regards,  Mike.
0
Question by:meverest
    7 Comments
     
    LVL 9

    Accepted Solution

    by:
    For an example, see this other question/answer: http://www.experts-exchange.com/Databases/PostgreSQL/Q_21112620.html
    0
     
    LVL 9

    Expert Comment

    by:rjkimble
    I think the simple answer to your question is that a trigger executes a function, not a SQL statement. You write a suitable function, one that executes the necessary SQL.
    0
     
    LVL 9

    Expert Comment

    by:rjkimble
    Here's the PostgreSQL 7.4 trigger documentation on line: http://www.postgresql.org/docs/current/static/triggers.html
    0
     
    LVL 37

    Author Comment

    by:meverest
    >> For an example, see this other question/answer: http://www.experts-exchange.com/Databases/PostgreSQL/Q_21112620.html

    yes, thanks for that - it appears that my problem is now "how to define language ' plpgsql'"  I'll start some research down that path, any pointers appreciated.

    >> I think the simple answer to your question is that a trigger executes a function, not a SQL statement. You write a suitable function, one that executes the necessary SQL.

    yes, i figured out that much! ;-)  but when i try to craete a function using language 'sql' i could not make it return type trigger.

    >> Here's the PostgreSQL 7.4 trigger documentation on line

    Thanks, yes - i have been going through those, but there is no example of using sql functions for triggers.

    Regards,  Mike.
    0
     
    LVL 9

    Expert Comment

    by:rjkimble
    Here are the instructions for installing PL/pgSQL into a database: http://www.postgresql.org/docs/7.4/static/xplang.html

    The "createlang" program is a separate program that should be installed as part of PostgreSQL. It's the easiest way to add PL/pgSQL to a database. You should run it from a shell prompt/command line prompt.
    0
     
    LVL 9

    Expert Comment

    by:rjkimble
    Another note: I think you're correct that you can't use the "SQL" language to write triggers. You have to use PL/pgSQL or one of the other languages. I'm not sure which all support triggers. It may be that the trigger function has to be written in PL/pgSQL. You can call functions written in other languages from inside the trigger function.

    Regards,

    .... Bob
    0
     
    LVL 37

    Author Comment

    by:meverest
    the plpgsql mechanism was what was needed - the accepted comment was the correct answer.

    THANKS! :)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now