-
help combining text and data in a trigger
I wrote the following trigger:
create or replace trigger AXIUM.ABNORMAL_LMT
after insert or update of "FormCode", "AnsListDesc" on AXIUM.PITEM
for each row
when (new."FormCode" ='AMEDHX' and new."AnsListDesc" Like 'Abnormal%')
declare
v_patient NUMBER(10);
v_chart CHAR(50 CHAR);
v_first CHAR(50 CHAR);
v_last CHAR(50 CHAR);
v_msgsnt NUMBER(10);
v_text CHAR(50 CHAR);
begin
select "Patient" into v_patient from abnormal_lmt where "Patient"=:new."Patient";
exception
when no_data_found then v_patient := '';
if
v_patient = :new."Patient"
then
return;
else
select "Chart" into v_chart from Patient where "Patient"=:new."Patient";
select "First" into v_first from Patient where "Patient"=:new."Patient";
select "Last" into v_last from Patient where "Patient"=:new."Patient";
insert into abnormal_lmt ("Patient", "Chart", "First", "Last") values (:new."Patient", v_chart, v_first, v_last);
select "First" ||"Last" into v_text from abnormal_lmt where "Patient"=:new."Patient";
insert into message values (MESSAGE_MESSAGE_SEQ.nextVal, trunc(sysdate), '552','14980', 'Abnormal limit selected on Head and Neck', v_text, '0', 'Jay Jabour', '0','0','0');
select max("Message") into v_msgsnt from message where "Recipients"='Jay Jabour';
insert into msgsent values ( v_msgsnt, '20240', 'None', 0,0);
end if;
end;
that fires after the pitem table is updated or inserted. When the formcode is amedhx and the anslistdesc is abnormal then the triger writes to a table called abnormal_lmt. the table has 4 fields chart first last and patient. I also want the trigger to insert into 2 other tables. However I want it to write the values of 3 fields into one field in the message table. the message table has one field called text. it is a varchar2 4000 bytes. I want it to put the following in that feild:
First Last chart has selected an abnormal limit on the head and neck form.
where first is the value of the first field in the abnormal table
last is the last field in the table
and chart is the chart field
and the rest is just text. below is the part of the trigger that I think would do it, however it doesn't.
select "First" ||"Last" || "Chart" ||' Has blah blah blah' into v_text from abnormal_lmt where "Patient"=:new."Patient";
when I make a change in the application that would activate the trigger the app just crashes.
however when I replace this part of the trigger with this it works but it is not what i want.
select "First" into v_text from abnormal_lmt where "Patient"=:new."Patient";
because this just puts the first name in that field.
so I just know this is my problem code:
select "First" ||"Last" || "Chart" ||' Has blah blah blah' into v_text from abnormal_lmt where "Patient"=:new."Patient";
-
It looks to me trigger already has these values as local variables...
How about v_first || v_last || v_chart ?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I tried this:
select v_first || v_last || v_chart into v_text from dual;
with this the trigger compiled fine but the app crashes
I tried this
v_first || v_last || v_chart := v_text;
but the trigger would not complile with out the following errror:
encountered the symbol "|" when expecting one of the following : := . ( @ % ;
so I am not sure how to use the variables to my advantage.
-
I also tried:
dbms_output.put_line ( v_first || v_last || v_chart);
and tried callling that in the insert:
insert into message values (MESSAGE_MESSAGE_SEQ.nextVal, trunc(sysdate), '552','14980', 'Abnormal limit selected on Head and Neck', dbms_output.get_line (:buffer);, '0', 'Jay Jabour', '0','0','0');
but the trigger compiles with this error:
bad bind variable 'BUFFER'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|