Re: My Trigger is not working :(

DECLARE section not contain variable AD_TABLE_ID

2011/9/9, Waqar Azeem <waqarazeem.private@gmail.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