My Trigger is not working :(

From: Waqar Azeem <waqarazeem(dot)private(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: My Trigger is not working :(
Date: 2011-09-09 12:27:41
Message-ID: CAPon0WmRwqQPRBnC3StNTnV2p9HJJGtkHT62rSn0t4SwckGNYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message shuaixf 2011-09-09 12:53:38 Re: how can I get the length of columns of a table by system tables/views
Previous Message Andreas 2011-09-07 01:04:07 How can I inherit constraints?