Solved

TSQL to PL/SQL

Posted on 2013-12-10
8
235 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
8 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 250 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 31

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 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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 31

Expert Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now