Solved

TSQL to PL/SQL

Posted on 2013-12-10
8
242 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 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 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 32

Expert Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server / Update DB? 22 36
SQL Quer 4 21
how to restore or keep sql2000  backups useful... 2 12
job schedule 8 18
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
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…

860 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