Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

trigger lang SQL?

Posted on 2004-10-28
7
Medium Priority
?
2,780 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
Comment
Question by:meverest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 9

Accepted Solution

by:
rjkimble earned 2000 total points
ID: 12440906
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
ID: 12440913
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
ID: 12440925
Here's the PostgreSQL 7.4 trigger documentation on line: http://www.postgresql.org/docs/current/static/triggers.html
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 37

Author Comment

by:meverest
ID: 12440983
>> 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
ID: 12441166
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
ID: 12441186
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
ID: 12441538
the plpgsql mechanism was what was needed - the accepted comment was the correct answer.

THANKS! :)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

604 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