Trigger in Oracle

amalia.stan · #1 (**permalink**) 12-11-12, 09:19

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!

shammat · #2 (**permalink**) 12-11-12, 10:06

Quote:

Originally Posted by amalia.stan

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.

Pat Phelan · #3 (**permalink**) 12-11-12, 13:18

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