Solved

[HELP] Stored Procedure, Trigger, Trigger Function

Posted on 2004-08-13
14
12,769 Views
Last Modified: 2012-06-27
Platform: Postgre 7.x and PgAdmin III

tbltest table
Code:
CREATE TABLE public.tbltest
(
  test_id int2 NOT NULL DEFAULT 0,
  name text,
  CONSTRAINT tbltest_pkey PRIMARY KEY (test_id)
) WITH OIDS;

tblresult table
Code:
CREATE TABLE public.tblresult
(
  result_id int2 NOT NULL DEFAULT 0,
  name text,
  found bool,
  position int8,
  CONSTRAINT tblresult_pkey PRIMARY KEY (result_id)
) WITH OIDS;

trigger
Code:
CREATE TRIGGER tr_log_update
  BEFORE INSERT
  ON public.tbltest
  FOR EACH ROW
  EXECUTE PROCEDURE public.log_update();

trigger function
Code:
CREATE OR REPLACE FUNCTION public.log_update()
  RETURNS trigger AS
'BEGIN
      UPDATE tblresult SET found = TRUE WHERE name = NEW.name;
RETURN NULL;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

Here is what I want,
1. Insert a new entry to table tbltest with id and name
2. Trigger started, run stored procedure.
3. If the name on table tblresult is found and match, the flag(boolean) will set to true.

Error when run this SQL
INSERT INTO tbltest VALUES (123, 'beta');

Code:
WARNING:  Error occurred while executing PL/pgSQL function log_update
WARNING:  line 2 at SQL statement
ERROR:  parser: parse error at or near "$1" at character 23

Thanks in advance
0
Comment
Question by:engtat
  • 5
  • 5
  • 4
14 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 11791632
Once in fuinction public.log_update TRIGGER context does not exist.  So NEW.name does not compute, does not compute, does not compute .....
0
 
LVL 1

Author Comment

by:engtat
ID: 11806887
I do not understand, can you correct my code?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11821983
I could but I won't.
Use the documentation to implement s simple example and learn from that.
0
 
LVL 1

Author Comment

by:engtat
ID: 11827081
I try to follow the documentation, but failed.
0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11827429
I think the problem is that what you want done appears to require a relatively substantial development effort (maybe a couple hours), and this is really just a forum for experts to offer some help. If an expert has access to substantially similar code, then it might make sense to offer it in an answer, but writing and testing a bunch of code for some points is probably not worthwhile. Even you don't seem to place much value on the effort, inasmuch as you are assigning it a meager 50 points. However, even for 500 points I wouldn't want to tackle this one. It's just too much effort. Mayber another expert has already done something similar or has a better approach and will be able to help you.

You might want to consider finding and hiring a local consultant to assist you.
0
 
LVL 1

Author Comment

by:engtat
ID: 11827828
sorry I did not know the rules here, this is my second time here. I will increase the points now.
0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11828220
OK. I found your problem. It turns out that earthman2's idea was wrong, which I didn't expect, because he's usually right. "NEW" DOES EXIST in the trigger context. However, you have made a very bad choice in naming one of your columns "found" -- it's a PostgreSQL reserved word in PL/PGSQL. I suggest strongly that you change the name of that column. I tried fixing your version by quoting the column name, but all my efforts along those lines failed. However, I found a clumsy way that works, but it's very fragile:

CREATE OR REPLACE FUNCTION public.log_update( ) RETURNS trigger AS '
    BEGIN
        EXECUTE ''UPDATE tblresult SET found = TRUE WHERE name = '''' + NEW.name + '''''';
        RETURN NULL;
    END;
' LANGUAGE 'plpgsql' VOLATILE;

This works because "found" is now part of the string that you execute dynamically. Unfortunately, NEW doesn't exist in the context of that command, so you have to compute the entire string that you want the server to execute. As I said, however, I think this is a VERY BAD approach. I STRONGLY RECOMMEND that you rename the "found" column in your tblresult table.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:engtat
ID: 11828837
OK, I will try your solution, I know 'found' is really a bad naming, but this table I created purposely to try how stored procedure works in postgreSQL.
I will update here, and assign the points if it works for me, thanks in advance.
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 140 total points
ID: 11848263
drop table tbltest;

CREATE TABLE tbltest
(
 test_id int2 primary key,
 name text
);

drop table tblresult;
CREATE TABLE tblresult
(
 result_id serial,
 name text,
 found_indicator bool,
 position int8
);


CREATE OR REPLACE FUNCTION public.log_update()
 RETURNS trigger AS
'BEGIN
    UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
RETURN NULL;
END;
'
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tr_log_update
 BEFORE INSERT
 ON public.tbltest
 FOR EACH ROW
 EXECUTE PROCEDURE public.log_update();

insert into tblresult( name, found_indicator, position ) values ( 'beta', false, 1 );

INSERT INTO tbltest VALUES (123, 'beta');

INSERT 0 0
select * from tblresult;
result_id | name | found_indicator | position
-----------+------+-----------------+----------
         1 | beta | t               |        1
(1 row)


0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11848830
This script and the resulting output demonstrates conclusively that the "found" column is the source of the problem, just as I indicated in my earlier comment. It also demonstrates that the first comment about the trigger context was wrong, which I also indicated in my earlier comment. Note that the result_id column in the tblresult table has been changed from int2 to serial, but that doesn't really change the validity of the demonstration.

Note also that no rows will ever be entered into the tbltest table, because the trigger always returns NULL, which causes PostgreSQL to abort the insert.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11852941
I guess you realliy want to insert data into tblresult then you return NEW not null.

CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
   UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11853304
>> I guess you realliy want to insert data into tblresult then you return NEW not null.

Exactly. My guess is that either engtat doesn't want to insert data into tbltest or that the proposed trigger has been written for testing purposes only. Another approach could be this:

CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
    UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
    IF FOUND THEN
        RETURN NEW;
    ELSE
        RETURN NULL;
    END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

This version causes the new row to be inserted unless the name value is not found in the tblresult table.
0
 
LVL 1

Author Comment

by:engtat
ID: 11866716
Thanks for everyone! rjkimble and earthman2 provided me very good explaination, unfortunately I cannot reward you guys at same time. I just assigned all my points to earthman2 because he was the first answering me from plain to full answer. thanks ya!
0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11867163
Amazing.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

18 Experts available now in Live!

Get 1:1 Help Now