[SQL] My Trigger is not working :(

[Waqar Azeem]

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();