Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TSQL to PL/SQL

Posted on 2013-12-10
8
Medium Priority
?
251 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

604 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