Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    20

    Trigger in Oracle

    Hello!

    I have the next view:

    Code:
     create or replace view RATA_AMC_NOUA AS
    SELECT
    DGS_RETEA.GA_AMC.ZZNRDEFECTE,
    DGS_RETEA.GA_AMC.ZZRATA_AMC,
    DGS_RETEA.GA_AMC.ZZGIS_ID,
    DGS_RETEA.GA_PIPE.FID,
    DGS_RETEA.GA_PIPE.PIPE_LENGTH,
    DGS_RETEA.GA_PIPE.ID_LOCATION_TYPE,
    ((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL)-
    nvl((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID and
    DGS_RETEA.GA_PIPE.ID_LOCATION_TYPE=2 GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL),0)) as Lungime_calcul_AMC,
    case when
    (((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL)-
    nvl((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID and
    DGS_RETEA.GA_PIPE.ID_LOCATION_TYPE=2 GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL),0)))=0
    then 0
    else
    (DGS_RETEA.GA_AMC.ZZNRDEFECTE/((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL)-
    nvl((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID and
    DGS_RETEA.GA_PIPE.ID_LOCATION_TYPE=2 GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL),0)))*1000
    END Rata_noua
    FROM
    DGS_RETEA.GA_AMC
    left JOIN DGS_RETEA.GA_PIPE ON DGS_RETEA.GA_AMC.ZZGIS_ID = DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL
    where GA_PIPE.ID_FUNCTION=1 and DGS_RETEA.GA_AMC.ZZNRDEFECTE<>0 and
    nvl((select sum(DGS_RETEA.GA_PIPE.PIPE_LENGTH) a from DGS_RETEA.GA_PIPE where DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL=DGS_RETEA.GA_AMC.ZZGIS_ID and DGS_RETEA.GA_PIPE.ID_LOCATION_TYPE=2 GROUP BY DGS_RETEA.GA_PIPE.ID_PIPE_VIRTUAL),0)<>0
    I want that everitime in this view I have a new row for that PIPE.FID to insert in update in ga_pipe rata_amc? Is it posibile to create a trigger on a view?
    Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,811
    Quote Originally Posted by amalia.stan View Post
    Is it posibile to create a trigger on a view?
    Yes - at least in Oracle. But you posted in the MySQL forum. So which DBMS are you using?

    If you did post in the wrong forum and you do use Oracle check out the manual for "INSTEAD OF" triggers. It's all documented there including an example on how to do it.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,836
    Oracle bought MySQL in 2009, so the MySQL database actually is an Oracle product.

    Depending on which MySQL version you are running you may be able to create the desired functionality against a table, but so far MySQL doesn't support triggers against views.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2011
    Posts
    20
    I'm using Oracle 11 g. I think my view doesen't suport INSTEAD OF TRIGGER.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,419
    Instead of triggers do not work on selects through views. Put a trigger on GA_PIPE to do this
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •