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.
LVL 37
meverestAsked:
Who is Participating?
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.