Hi All, I have written below trigger and applied on the table but it is giving syntax error when it gets invoked. Could you please help me what is wrong? I have given complete details here:
*my trigger function:* ** CREATE OR REPLACE FUNCTION techdb_logtable_trigger() RETURNS trigger AS $BODY$ my $id = $_TD->{new}{'id'}; my $query = " INSERT INTO log_table(id) * SELECT $1 EXCEPT SELECT id --> throwing error for here: syntax error at or near "EXCEPT"* FROM loggingtable WHERE id = $1;"; if (exists($_SHARED{$query})) { $plan = $_SHARED{$query}; } else { $plan = spi_prepare($query, 'INTEGER'); $_SHARED{$query} = $plan; } spi_exec_prepared($plan, '$id'); $BODY$ LANGUAGE 'plperl' VOLATILE SECURITY DEFINER *Tables and trigger definitions:* CREATE TABLE techdb_table( id integer NOT NULL, info varchar NOT NULL ); CREATE TABLE log_table( id integer NOT NULL, txid integer NOT NULL DEFAULT txid_current(), txtime timestamp NOT NULL DEFAULT transaction_timestamp()); CREATE TRIGGER techdb_trigger BEFORE INSERT ON techdb_table FOR EACH ROW EXECUTE PROCEDURE techdb_logtable_trigger(); *Error message:* Executing "insert into techdb_table values(1, 'test');" command gives below error. ERROR: error from Perl function "techdb_logtable_trigger": syntax error at or near "EXCEPT" at line 15. ********** Error ********** ERROR: error from Perl function "techdb_logtable_trigger": syntax error at or near "EXCEPT" at line 15. SQL state: XX000 Thanks, Jignesh