I have listed functions, triggers , tables and view for your reference.
Thanks for helping me out
Regards
CREATE OR REPLACE FUNCTION fnc_loadDenormdata()
RETURNS trigger AS
$BODY$
DECLARE
v_transactionid numeric;
v_startdate text;
v_enddate text;
v_statuscode character varying(10);
v_enddate_ts timestamp without time zone;
v_canceldate_ts timestamp without time zone;
v_firstname character varying(100);
v_lastname character varying(100);
v_phone character varying(20);
v_fax character varying(20);
v_usercomments character varying;
BEGIN
if(TG_OP='INSERT') THEN
v_transactionid=NEW.transactionid;
select transactionid,
startdate,enddate,statuscode,enddate_ts,canceldate_ts,firstname,lastname,phone,fax
into
v_transactionid,v_startdate,v_enddate,v_statuscode,v_enddate_ts,v_canceldate_ts,v_firstname,v_lastname,v_phone,v_fax,v_usercomments
from v_search where transactionid=v_transactionid ;
insert into t_search values(
v_transactionid,v_startdate,v_enddate,v_statuscode,v_enddate_ts,v_canceldate_ts,v_firstname,v_lastname,v_phone,v_fax);
return NEW;
elsif(TG_OP='UPDATE') then
v_transactionid=OLD.transactionid;
select transactionid,
startdate,enddate,statuscode,enddate_ts,canceldate_ts,firstname,lastname,phone,fax
into
v_transactionid,v_startdate,v_enddate,v_statuscode,v_enddate_ts,v_canceldate_ts,v_firstname,v_lastname,v_phone,v_fax,v_usercomments
from v_search where transactionid=v_transactionid ;
update t_search set
issuedate=v_issuedate,startdate=v_startdate,enddate=v_enddate,statuscode=v_statuscode,enddate_ts=v_enddate_ts,canceldate_ts=v_canceldate_ts,
firstname=v_firstname,lastname=v_lastname,phone=v_phone,fax=v_fax,comments=v_usercomments
where transactionid=v_transactionid ;
return OLD;
END IF;
EXCEPTION
when others then
insert into tb_DEBUG
values
(nextval('seq_errorid'),current_timestamp,'fnc_fnc_loadDenormdata',SQLSTATE||':
'||SQLERRM);
raise exception 'fnc_loadDenormdata Failed: %-%', SQLSTATE, SQLERRM;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;
----------- these are triggers
CREATE TRIGGER trig_loadDenormdata
AFTER INSERT OR UPDATE
ON t_items
FOR EACH ROW
EXECUTE PROCEDURE fnc_loadDenormdata();
CREATE TRIGGER trig_loadDenormdata
AFTER INSERT OR UPDATE
ON t_comments
FOR EACH ROW
EXECUTE PROCEDURE fnc_loadDenormdata();
-------------------------
CREATE TABLE t_comments
(
transactionid numeric(9),
usercomments character varying,
publiccomments character varying,
)
WITH (
OIDS=FALSE
)
CREATE TABLE t_items
(
transactionid numeric,
startdate timestamp without time zone,
statuscode character varying,
enddate timestamp without time zone,
canceldate timestamp without time zone,
fax character varying(20),
phone character varying(20),
userid numeric,
)
WITH (
OIDS=FALSE
)
create view v_search as
SELECT itm.transactionid, to_char(itm.issuedate::timestamp with time zone,
'MM/DD/YYYY HH24MI'::text) AS issuedate,
to_char(itm.startdate::timestamp with time zone, 'MM/DD/YYYY HH24MI'::text)
AS startdate,
to_char(itm.enddate::timestamp with time zone, 'MM/DD/YYYY HH24MI'::text) AS
enddate, itm.statuscode, itm.enddate AS enddate_ts, itm.canceldate AS
canceldate_ts, usr.firstname, usr.lastname, itm.phone, itm.fax,
com.usercomments,
itm.lastupdatedate AS last_update_timestamp, btrim(
FROM t_items itm, t_comments com, t_user usr
WHERE itm.transactionid = com.transactionid AND itm.userid = usr.userid ;
------------------------------
On Mon, Nov 8, 2010 at 1:54 PM, hubert depesz lubaczewski <dep...@depesz.com
> wrote:
> On Mon, Nov 08, 2010 at 01:45:49PM -0500, akp geek wrote:
> > Hi All -
> >
> > Can you please share your thoughts and help me ?
> >
> > 1. I have 4 ( T1, T2 , T3, T4 ) tables where I have the data
> from
> > a transactional system
> >
> > 2. I have created one more table D1 to denormalize the data
> from
> > the 4 tables ( T1, T2 , T3, T4 )
> >
> > 3. I have created function that returns trigger (TGR_1) .
> >
> > 4. I have create one trigger ( TGR_1) after insert or update on
> T1
> > & T2.
> >
> > 5. Now when I insert data from the application, the save
> function
> > will write data to T1 & T2.
> >
> > 6. The problem is the table D1 gets 2 rows, One with correct
> data
> > and the other is Blank row. I am not able to understand why I am getting
> a
> > blank row.
> >
> > Any thoughts on this? Appreciate your help
>
> well - without any kind of code to look at it's impossible to guess what
> might be wrong.
>
> please provide function source, so we can see if there is a problem.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog:
> http://www.depesz.com/
> jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl /
> gg:6749007
>
