Обсуждение: My Trigger is not working :(
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();
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
Thanks...
--
Thanks & Best Regards,
Waqar Azeem
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.p@gmail.com>
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Thanks & Best Regards,
Waqar Azeem