Re: My Trigger is not working :(

Thanks...
After putting some code here ... starts getting this error ...

SQL statement "INSERT INTO ad_changelog( ad_changelog_id, ad_session_id,
ad_table_id, ad_column_id, ad_client_id, ad_org_id, isactive, created,
createdby, updated, updatedby, record_id, oldvalue, newvalue, undo, redo,
iscustomization, trxname, description, eventchangelog) VALUES ( $1 +1, $2
, $3 , $4 , $5 , $6 , $7 , $8 , $9 , $10 , $11 , $12 , $13 , $14
, $15 , $16 , $17 , $18 , $19 , $20 )"
PL/pgSQL function "oms_changelog" line 19 at SQL statement

-- code --
I need to duplicating the record that is inserted with some minor change. I
simple add in insert into (see below)

int_AD_COLUMN_ID = 0;
int_AD_TABLE_ID = 0;

int_AD_TABLE_ID = NEW.AD_TABLE_ID;
int_AD_COLUMN_ID = NEW.AD_COLUMN_ID;

IF (NEW.ad_table_id=1000057) THEN
SELECT ad_table_id, ad_column_id INTO int_AD_COLUMN_ID, int_AD_TABLE_ID
FROM ad_column WHERE ad_table_id=1000031 AND columnname LIKE (SELECT
c.columnname FROM ad_column c WHERE c.ad_column_id=int_AD_COLUMN_ID);
elsif (NEW.ad_table_id=1000058) then
SELECT ad_table_id, ad_column_id INTO int_AD_COLUMN_ID,
int_AD_TABLE_ID FROM ad_column WHERE ad_table_id=1000032 AND columnname LIKE
(SELECT c.columnname FROM ad_column c WHERE
c.ad_column_id=int_AD_COLUMN_ID);
elsif (NEW.ad_table_id=1000059) then
SELECT ad_table_id, ad_column_id INTO int_AD_COLUMN_ID,
int_AD_TABLE_ID FROM ad_column WHERE ad_table_id=1000033 AND columnname LIKE
(SELECT c.columnname FROM ad_column c WHERE
c.ad_column_id=int_AD_COLUMN_ID);
end if;

IF (int_AD_COLUMN_ID > 1000000) THEN
INSERT INTO ad_changelog(
ad_changelog_id, ad_session_id, ad_table_id, ad_column_id,
ad_client_id,
ad_org_id, isactive, created, createdby, updated, updatedby,
record_id, oldvalue, newvalue, undo, redo, iscustomization, trxname,
description, eventchangelog)
VALUES (
NEW.ad_changelog_id+1, NEW.ad_session_id, int_AD_TABLE_ID,
int_AD_COLUMN_ID, NEW.ad_client_id,
NEW.ad_org_id, NEW.isactive, NEW.created, NEW.createdby, NEW.updated,
NEW.updatedby,
NEW.record_id, NEW.oldvalue, NEW.newvalue, NEW.undo, NEW.redo,
NEW.iscustomization, NEW.trxname,
NEW.description, NEW.eventchangelog);
END IF;

2011/9/9 pasman pasmański <pasman(dot)p(at)gmail(dot)com>

> DECLARE section not contain variable AD_TABLE_ID
>
> 2011/9/9, Waqar Azeem <waqarazeem(dot)private(at)gmail(dot)com>:
> > I think i missed some basics ...
> >
> >
> > ERROR: column "ad_table_id" does not exist
> > Where: PL/pgSQL function "oms_changelog" line 21 at assignment
> >
> > --------------------------
> > trigger definition
> > --------------------------
> >
> > CREATE OR REPLACE FUNCTION oms_changelog()
> > RETURNS trigger AS
> > $BODY$
> > DECLARE
> > int_AD_COLUMN_ID numeric(10,0);
> > int_AD_TABLE_ID numeric(10,0);
> > BEGIN
> >
> > int_AD_COLUMN_ID = 0;
> > int_AD_TABLE_ID = 0;
> >
> > int_AD_TABLE_ID = NEW.AD_TABLE_ID;
> > int_AD_COLUMN_ID = NEW.AD_COLUMN_ID;
> >
> > IF (int_AD_COLUMN_ID > 1000000) THEN
> > NEW.ad_table_id = AD_TABLE_ID;
> > NEW.ad_column_id = AD_COLUMN_ID;
> > END IF;
> >
> > -- RAISE EXCEPTION '% cannot have a negative salary',
> NEW.empname;
> > RETURN NEW;
> > END;
> > $BODY$
> > LANGUAGE 'plpgsql' VOLATILE
> > COST 100;
> >
> >
> >
> > ----------------
> > Table definition:
> > ----------------
> >
> > CREATE TABLE ad_changelog
> > (
> > ad_changelog_id numeric(10,0) NOT NULL,
> > ad_session_id numeric(10,0) NOT NULL,
> > ad_table_id numeric(10,0) NOT NULL,
> > ad_column_id numeric(10,0) NOT NULL,
> > ad_client_id numeric(10,0) NOT NULL,
> > ad_org_id numeric(10,0) NOT NULL,
> > isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
> > created timestamp without time zone NOT NULL DEFAULT now(),
> > createdby numeric(10,0) NOT NULL,
> > updated timestamp without time zone NOT NULL DEFAULT now(),
> > updatedby numeric(10,0) NOT NULL,
> > record_id numeric(10,0) NOT NULL,
> > oldvalue character varying(2000),
> > newvalue character varying(2000),
> > undo character(1),
> > redo character(1),
> > iscustomization character(1) NOT NULL DEFAULT 'N'::bpchar,
> > trxname character varying(60),
> > description character varying(255),
> > eventchangelog character(1),
> > CONSTRAINT ad_changelog_pkey PRIMARY KEY (ad_changelog_id,
> ad_session_id,
> > ad_table_id, ad_column_id),
> > CONSTRAINT adcolumn_adchangelog FOREIGN KEY (ad_column_id)
> > REFERENCES ad_column (ad_column_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
> DEFERRED,
> > CONSTRAINT adsession_adchangelog FOREIGN KEY (ad_session_id)
> > REFERENCES ad_session (ad_session_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY
> DEFERRED,
> > CONSTRAINT adtable_adchangelog FOREIGN KEY (ad_table_id)
> > REFERENCES ad_table (ad_table_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
> DEFERRED,
> > CONSTRAINT ad_changelog_isactive_check CHECK (isactive = ANY
> > (ARRAY['Y'::bpchar, 'N'::bpchar])),
> > CONSTRAINT ad_changelog_iscustomization_check CHECK (iscustomization =
> ANY
> > (ARRAY['Y'::bpchar, 'N'::bpchar]))
> > )
> > WITH (
> > OIDS=FALSE
> > );
> > ALTER TABLE ad_changelog OWNER TO adempiere;
> >
> > -- Index: ad_changelog_speed
> >
> > -- DROP INDEX ad_changelog_speed;
> >
> > CREATE INDEX ad_changelog_speed
> > ON ad_changelog
> > USING btree
> > (ad_table_id, record_id);
> >
> >
> > -- Trigger: oms_changelog on ad_changelog
> >
> > -- DROP TRIGGER oms_changelog ON ad_changelog;
> >
> > CREATE TRIGGER oms_changelog
> > BEFORE INSERT
> > ON ad_changelog
> > FOR EACH ROW
> > EXECUTE PROCEDURE oms_changelog();
> >
>
>
> --
> ------------
> pasman
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--
Thanks & Best Regards,
Waqar Azeem