PostGreSQL Trigger Help

vinodjayachandran · #1 (**permalink**) 11-03-11, 06:32

Hi

I need some help in writing PostGreSQL Trigger.
I need to write trigger as below

CREATE TRIGGER WMTbhk242b AFTER INSERT ON public.testtable FOR EACH ROW
BEGIN
INSERT INTO WMBbhk242b (c1, wm_rowid) VALUES (:new.c1, WMSbhk242b.NEXTVAL);
END;". "

But postgre expects EXECUTE PROCEDURE . I create the trigger dynamically from my code, so I am unable to create a procedure prior to creating this trigger

Wedgetail · #2 (**permalink**) 11-24-11, 07:22

Hi vinodjayachandran,

PostgreSQL requires a Function to be defined as a Trigger procedure, then added to a Table as a Trigger.

eg. Create the Function

Code:

Create or Replace Function WMTbhk242b () RETURNS TRIGGER AS $WMTbhk242b$
BEGIN
      INSERT INTO WMBbhk242b (c1, wm_rowid) VALUES (:new.c1, WMSbhk242b.NEXTVAL);
END;
$WMTbhk242b$ LANGUAGE plpgsql STABLE;

Add the Trigger to the Table

Code:

CREATE TRIGGER trigger_name AFTER INSERT ON public.testtable
  FOR EACH ROW EXECUTE PROCEDURE WMTbhk242b();

shammat · #3 (**permalink**) 11-24-11, 08:06

Quote:

Originally Posted by vinodjayachandran

I create the trigger dynamically from my code, so I am unable to create a procedure prior to creating this trigger

Well, then your code needs to create the procedure and the trigger dynamically.

Internet Services

Web Development

Digital Marketing

Consumer Tech