[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2782
  • Last Modified:

trigger lang SQL?

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
meverest
Asked:
meverest
  • 5
  • 2
1 Solution
 
rjkimbleCommented:
For an example, see this other question/answer: http://www.experts-exchange.com/Databases/PostgreSQL/Q_21112620.html
0
 
rjkimbleCommented:
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
 
rjkimbleCommented:
Here's the PostgreSQL 7.4 trigger documentation on line: http://www.postgresql.org/docs/current/static/triggers.html
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
meverestAuthor Commented:
>> 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
 
rjkimbleCommented:
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
 
rjkimbleCommented:
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
 
meverestAuthor Commented:
the plpgsql mechanism was what was needed - the accepted comment was the correct answer.

THANKS! :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now