Search Postgresql Archives

Re: need help with Triggers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <depesz@xxxxxxxxxx> 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
: depesz@xxxxxxxxxx / aim:depeszhdl / skype:depesz_hdl / gg:6749007


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux