Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

TSQL to PL/SQL

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
aytit
Asked:
aytit
2 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
awking00Commented:
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
 
aytitAuthor Commented:
I need the update a field after insert
0
 
sdstuberCommented:
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
 
awking00Commented:
>>I need the update a field after insert<<
Which field, where, with what?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now