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
Start Free Trial
