Hi all,
I am writing a row trigger called after insert, update and delete operations on a table, lets call this table 'togo', the table definition contains a not null field having a default clause. On running the trigger (performing an insert on 'togo') I get an error which indicates that the insert attempted to insert a value (from the NEW record returned from function on which the trigger is based) into the not null field having a default clause.
This could mean that the sql insert statement called after return of the trigger did not make use of field names (even though) I had supplied the field names in my hand written insert statement on which the insert statement was called.
On solution (am trying out but still having managed to find out how to do this) is maybe to use a second variable of type RECORD where I provide the value of the default value as it's first field then append all the fields of the (internal) NEW RECORD variable?
Perhaps my table definition and trigger definition may help explain (included below)
drop sequence if exists data_update_audit_seq cascade;
drop table if exists data_update_audit cascade;
DROP FUNCTION IF EXISTS process_data_update_audit() cascade;
drop table if exists togo cascade;
drop sequence if exists togo_seq;
drop trigger if exists trig_process_data_update_audit on togo cascade;
create sequence data_update_audit_seq;
create table data_update_audit
(
id int not null default nextval('data_update_audit_seq')
,table_name text not null
,operation char(1) NOT NULL
,stamp timestamp NOT NULL
,userid text NOT NULL
,old_or_new char(1)not null
,row_data text null
,primary key(id)
);
CREATE OR REPLACE FUNCTION process_data_update_audit() RETURNS TRIGGER AS $data_update_audit$
DECLARE
new2 RECORD;
BEGIN
--
-- Create a row in data_update_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
RAISE NOTICE 'TG_TABLE_SCHEMA:%, TG_TABLE_NAME:%',TG_TABLE_SCHEMA,TG_TABLE_NAME;
IF (TG_OP = 'DELETE') THEN
INSERT INTO data_update_audit SELECT 'D', now(), user, 'O', OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO data_update_audit SELECT 'U', now(), user, 'O', OLD.*;
INSERT INTO data_update_audit SELECT 'U', now(), user, 'N', NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
--INSERT INTO data_update_audit SELECT 'I', now(), user, 'O', OLD.*;
INSERT INTO data_update_audit SELECT 'I', now(), user, 'N', NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$data_update_audit$ LANGUAGE plpgsql;
create sequence togo_seq;
create table togo(id int not null default nextval('togo_seq'),sname text,hours int);
create trigger trig_process_data_update_audit after insert or update or delete on togo
for each row execute procedure process_data_update_audit()
;
--now when I attempt to issue the command:
insert into togo(sname,hours)values('Allan',8);
--I get the error below
ERROR: invalid input syntax for integer: "I"
CONTEXT: SQL statement "INSERT INTO data_update_audit SELECT 'I', now(), user, 'N', $1 .*"
PL/pgSQL function "process_data_update_audit" line 18 at SQL statement
test2=>
-- a quick look at the logs yields the statement below:
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>NOTICE: TG_TABLE_SCHEMA:public, TG_TABLE_NAME:togo
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>ERROR: invalid input syntax for integer: "I"
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>CONTEXT: SQL statement "INSERT INTO data_update_audit SELECT 'I', now(), user, 'N', $1 .*"
PL/pgSQL function "process_data_update_audit" line 18 at SQL statement
<2008-01-10 12:20:58.241 SAST-test-test2-INSERT>STATEMENT: insert into togo(sname,hours)values('Allan',8);
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
