Re: need help with Triggers
| От | akp geek |
|---|---|
| Тема | Re: need help with Triggers |
| Дата | |
| Msg-id | AANLkTimogTQi5Vt8u76-FnoLfqAj0LtpzSFKn=2g9TQH@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: need help with Triggers (hubert depesz lubaczewski <depesz@depesz.com>) |
| Список | pgsql-general |
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@depesz.com> wrote:
well - without any kind of code to look at it's impossible to guess whatOn 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
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@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
В списке pgsql-general по дате отправления: