?
Solved

TSQL to PL/SQL

Posted on 2013-12-10
8
Medium Priority
?
248 Views
Last Modified: 2014-10-01
I need the convert Tsql trigger to Pl/Sqli could anyone help me?

CREATE TRIGGER Rep2_update   ON  
      CA_VISIT_EVENT_ITEM
   AFTER INSERT
AS
BEGIN
      declare @event_item_id numeric(38,0),
      @visit_id numeric(38,0)
      declare
      @agreement_number varchar(6),
      @master_agreement_number varchar(6),
      @company_number varchar(20)
      select  @event_item_id = EVENT_ITEM_ID, @visit_id = VISIT_ID from inserted
      if @event_item_id = 289
      begin
            select @agreement_number = agreement_number from
            CA_VISIT where VISIT_ID = @visit_id

            select @master_agreement_number = master_agreement_number  from AGREEMENTS
            where AGREEMENT_NUMBER = @agreement_number
            if @master_agreement_number is not null
            begin
                  select  @company_number = company_number from agreements
                  where agreement_number = @master_agreement_number
            end
            else
            begin
                  select  @company_number = company_number from agreements
                  where agreement_number = @agreement_number
            end
                  update CA_VISIT_EVENT_ITEM
                  set CHAR_VALUE= @company_number
                  where VISIT_ID =@visit_id and EVENT_ITEM_ID = 289              
            
      end


END
GO
0
Comment
Question by:aytit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 1000 total points
ID: 39708408
Sorry, my TSQL is rusty, but I believe this should be a pretty close version of what you are trying to do.  I didn't check syntax, so there may be a couple of errors there, but it should be close.

create or replace trigger rep2_update
on ca_visit_item
after insert
declare
  v_master_agreement  agreements.master_agreement_number%type;
  v_agreement         ca_visit.agreement_number%type;
  v_comp_num          agreements.company_number%type;
begin
  if :new.event_item_id = 289 then
    select master_agreement_number
      into v_master_agreement
      from agreeemnts
     where agreement_number = :new.agreement_number;
    if v_master_agreement is not null then
      select company_number
        into v_comp_num
        from agreements
       where agreement_number = v_master_agreement;
    else
      select agreement_number
        into v_agreement
        from ca_visit
       where visit_id = :new.visit_id;
      select company_number
        into v_comp_num
        from agreements
       where agreement_number = v_agreement;
    end if;
    :new.char_value = v_comp_num;
end;
/

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39708677
Rather than trying to interpret T-SQL code, can you explain in plain language what it is you want your trigger to do after insert on CA_VISIT_EVENT_ITEM?
0
 
LVL 1

Author Comment

by:aytit
ID: 39708803
I need the update a field after insert
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 39708819
Small adjustment to johnsone's post above, the trigger as posted would be an after-statement level trigger which won't have access to the :new pseudo-record.  It need to be a row-level trigger.  Also need to change the order of a couple clauses

change

on ca_visit_item
after insert

Open in new window


to

after insert
on ca_visit_item
for each row

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39709153
>>I need the update a field after insert<<
Which field, where, with what?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question