database - techonthenet - trigger oracle update

¿Hay alguna manera de crear múltiples disparadores en un script?

Question

Intento crear múltiples disparadores con solo cargar un script en un espacio de trabajo Oracle DB / APEX y ejecutarlo una vez.

Aquí hay un breve guión en comparación con el que estoy tratando de usar:

    create or replace trigger "BI_TEC_ROLES"   
      before insert on "TEC_ROLES"               
      for each row  
    begin   
      if :NEW."ROLE_ID" is null then 
        select "TEC_ROLES_SEQ".nextval into :NEW."ROLE_ID" from dual; 
      end if; 
    end; 

    create or replace trigger "BI_TEC_STATUSES"   
      before insert on "TEC_STATUSES"               
      for each row  
    begin   
      if :NEW."STATUS_ID" is null then 
        select "TEC_STATUSES_SEQ".nextval into :NEW."STATUS_ID" from dual; 
      end if; 
    end; 

    create or replace trigger "BI_TEC_SUBS"   
      before insert on "TEC_SUBS"               
      for each row  
    begin   
      if :NEW."SUB_ID" is null then 
        select "TEC_SUBS_SEQ".nextval into :NEW."SUB_ID" from dual; 
      end if; 
    end; 

He intentado poner GO entre cada bloque individual, pero aún así solo creo el primer disparador, entonces me da un error para el segundo dicho:

    Error(7,1): PLS-00103: Encountered the symbol "CREATE" 

Espero que sea posible hacer esto. Muchas gracias por su tiempo e interés =)

Answer 1

Agregue una barra inclinada en una nueva línea después de cada activador para ejecutar el comando en el búfer:

create trigger...
...
end;
/

Answer 2

Coloque una barra inclinada ''/'' como el primer carácter en una línea en blanco entre cada declaración de activación. Este es el equivalente SQL * PLUS de ''go''.

Answer 3

Coloque una barra hacia adelante

/

entre las dos declaraciones en una línea separada.

Oracle entonces lo aceptará como una nueva declaración

Answer 4

Sí, podemos ejecutar múltiples procedimientos / desencadenantes / funciones en una sola secuencia de comandos usando FORWARD SLASH / dentro del archivo sql.

Como abajo:

create or replace trigger "BI_TEC_ROLES"   
      before insert on "TEC_ROLES"               
      for each row  
    begin   
      if :NEW."ROLE_ID" is null then 
        select "TEC_ROLES_SEQ".nextval into :NEW."ROLE_ID" from dual; 
      end if; 
    end; 

/
    create or replace trigger "BI_TEC_STATUSES"   
      before insert on "TEC_STATUSES"               
      for each row  
    begin   
      if :NEW."STATUS_ID" is null then 
        select "TEC_STATUSES_SEQ".nextval into :NEW."STATUS_ID" from dual; 
      end if; 
    end; 

/

   create or replace trigger "BI_TEC_SUBS"   
      before insert on "TEC_SUBS"               
      for each row  
    begin   
      if :NEW."SUB_ID" is null then 
        select "TEC_SUBS_SEQ".nextval into :NEW."SUB_ID" from dual; 
      end if; 
    end; 

/

Entonces Oracle lo considerará como una nueva instrucción / bloque.

Answer 5

--Parameter: 
--  @InclDrop bit   
--  Possible values 
--    0 - Script to drop the triggers is not generated.   
--    1 - Script to drip the triggers is generated. 

SET ansi_nulls ON 

go 

SET quoted_identifier ON 

go 


ALTER PROCEDURE [dbo].[Createscriptofalltriggers] 

@InclDrop BIT =1

AS 

DECLARE @SQL VARCHAR(8000), 
        @Text            NVARCHAR(4000), 
        @BlankSpaceAdded INT, 
        @BasePos         INT, 
        @CurrentPos      INT, 
        @TextLength      INT, 
        @LineId          INT, 
        @MaxID           INT, 
        @AddOnLen        INT, 
        @LFCR            INT, 
        @DefinedLength   INT, 
        @SyscomText      NVARCHAR(4000), 
        @Line            NVARCHAR(1000), 
        @UserName        SYSNAME, 
        @ObjID           INT, 
        @OldTrigID       INT 

SET nocount ON 
SET @DefinedLength = 1000 
SET @BlankSpaceAdded = 0 

IF @InclDrop <> 0 
  SET @InclDrop =1 

-- This Part Validated the Input parameters   
DECLARE @Triggers TABLE 
  ( 
     username SYSNAME NOT NULL, 
     trigname SYSNAME NOT NULL, 
     objid    INT NOT NULL 
  ) 
DECLARE @TrigText TABLE 
  ( 
     objid    INT NOT NULL, 
     lineid   INT NOT NULL, 
     linetext NVARCHAR(1000) NULL 
  ) 

INSERT INTO @Triggers 
            (username, 
             trigname, 
             objid) 
SELECT DISTINCT A.NAME, 
                B.NAME, 
                B.id 
FROM   dbo.sysusers A, 
       dbo.sysobjects B, 
       dbo.syscomments C 
WHERE  A.uid = B.uid 
       AND B.type = ''Tr'' 
       AND B.id = C.id 
       AND C.encrypted = 0 

IF EXISTS(SELECT C.* 
          FROM   syscomments C, 
                 sysobjects O 
          WHERE  O.id = C.id 
                 AND O.type = ''Tr'' 
                 AND C.encrypted = 1) 
  BEGIN 
      PRINT ''/*'' 

      PRINT ''The following encrypted triggers were found'' 

      PRINT ''The procedure could not write the script for it'' 

      SELECT DISTINCT A.NAME, 
                      B.NAME, 
                      B.id 
      FROM   dbo.sysusers A, 
             dbo.sysobjects B, 
             dbo.syscomments C 
      WHERE  A.uid = B.uid 
             AND B.type = ''Tr'' 
             AND B.id = C.id 
             AND C.encrypted = 1 

      PRINT ''*/'' 
  END 

DECLARE ms_crs_syscom CURSOR local forward_only FOR 
  SELECT T.objid, 
         C.text 
  FROM   @Triggers T, 
         dbo.syscomments C 
  WHERE  T.objid = C.id 
  ORDER  BY T.objid, 
            C.colid 
  FOR READ only 

SELECT @LFCR = 2 

SELECT @LineId = 1 

OPEN ms_crs_syscom 

SET @OldTrigID = -1 

FETCH next FROM ms_crs_syscom INTO @ObjID, @SyscomText 

WHILE @@fetch_status = 0 
  BEGIN 
      SELECT @BasePos = 1 

      SELECT @CurrentPos = 1 

      SELECT @TextLength = Len(@SyscomText) 

      IF @ObjID <> @OldTrigID 
        BEGIN 
            SET @LineID = 1 
            SET @OldTrigID = @ObjID 
        END 

      WHILE @CurrentPos != 0 
        BEGIN 
            --Looking for end of line followed by carriage return         
            SELECT @CurrentPos = Charindex(Char(13) + Char(10), @SyscomText, 
                                 @BasePos) 

            --If carriage return found         
            IF @CurrentPos != 0 
              BEGIN 

                  WHILE ( Isnull(Len(@Line), 0) + @BlankSpaceAdded 
                          + @CurrentPos - @BasePos + @LFCR ) > 
                        @DefinedLength 
                    BEGIN 
                        SELECT @AddOnLen = @DefinedLength - ( 
                                           Isnull(Len(@Line), 
                                           0 
                                           ) + 
                                           @BlankSpaceAdded ) 

                        INSERT @TrigText 
                        VALUES ( @ObjID, 
                                 @LineId, 
                                 Isnull(@Line, N'''') 
                                 + Isnull(Substring(@SyscomText, @BasePos, 
                                 @AddOnLen), 
                                 N'''')) 

                        SELECT @Line = NULL, 
                               @LineId = @LineId + 1, 
                               @BasePos = @BasePos + @AddOnLen, 
                               @BlankSpaceAdded = 0 
                    END 

                  SELECT @Line = Isnull(@Line, N'''') 
                                 + Isnull(Substring(@SyscomText, @BasePos, 
                                 @CurrentPos 
                                 -@BasePos + 
                                 @LFCR), 
                                        N'''') 

                  SELECT @BasePos = @CurrentPos + 2 

                  INSERT @TrigText 
                  VALUES( @ObjID, 
                          @LineId, 
                          @Line ) 

                  SELECT @LineId = @LineId + 1 

                  SELECT @Line = NULL 
              END 
            ELSE 
              --else carriage return not found         
              BEGIN 
                  IF @BasePos <= @TextLength 
                    BEGIN 
                        /*If new value for @Lines length will be > then the         
                        **defined length         
                        */ 
                        WHILE ( Isnull(Len(@Line), 0) + @BlankSpaceAdded 
                                + @TextLength - @BasePos + 1 ) > 
                              @DefinedLength 
                          BEGIN 
                              SELECT @AddOnLen = @DefinedLength - ( 
                                                 Isnull(Len(@Line), 
                                                 0 
                                                 ) + 
                                                 @BlankSpaceAdded ) 

                              INSERT @TrigText 
                              VALUES ( @ObjID, 
                                       @LineId, 
                                       Isnull(@Line, N'''') 
                                       + Isnull(Substring(@SyscomText, 
                                       @BasePos, 
                                       @AddOnLen), 
                                       N'''')) 

                              SELECT @Line = NULL, 
                                     @LineId = @LineId + 1, 
                                     @BasePos = @BasePos + @AddOnLen, 
                                     @BlankSpaceAdded = 0 
                          END 

                        SELECT @Line = Isnull(@Line, N'''') 
                                       + Isnull(Substring(@SyscomText, 
                                       @BasePos, 
                                       @TextLength 
                                       -@BasePos+1 
                                       ), N'''') 

                        IF Len(@Line) < @DefinedLength 
                           AND Charindex('' '', @SyscomText, @TextLength + 1) 
                               > 0 
                          BEGIN 
                              SELECT @Line = @Line + '' '', 
                                     @BlankSpaceAdded = 1 
                          END 
                    END 
              END 
        END 

      FETCH next FROM ms_crs_syscom INTO @ObjID, @SyscomText 
  END 

IF @Line IS NOT NULL 
  INSERT @TrigText 
  VALUES( @ObjID, 
          @LineId, 
          @Line ) 

CLOSE ms_crs_syscom 

PRINT ''-- You should run this result under dbo if your triggers belong to multiple users'' 

PRINT '''' 

IF @InclDrop = 1 
  BEGIN 
      PRINT ''-- Dropping the Triggers'' 

      PRINT '''' 

      SELECT ''If exists(Select * from sysObjects where id =Object_ID(''''['' 
             + username + ''].['' + trigname 
             + '']'''') and ObjectProperty(Object_ID(''''['' 
             + username + ''].['' + trigname + '']''''), ''''ISTRIGGER'''')=1)   Drop Trigger ['' 
             + username + ''].['' + trigname + ''] '' + Char(13) 
             + Char(10) + ''GO'' + Char(13) + Char(10) + Char(13) 
             + Char(10) 
      FROM   @Triggers 
  END 

PRINT ''----------------------------------------------'' 

PRINT ''-- Creation of Triggers'' 

PRINT '''' 

PRINT '''' 

DECLARE ms_users CURSOR local forward_only FOR 
  SELECT T.username, 
         T.objid, 
         Max(D.lineid) 
  FROM   @Triggers T, 
         @TrigText D 
  WHERE  T.objid = D.objid 
  GROUP  BY T.username, 
            T.objid 
  FOR READ only 

OPEN ms_users 

FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID 

WHILE @@fetch_status = 0 
  BEGIN 
      PRINT ''SetUser N'''''' + @UserName + '''''''' + Char(13) 
            + Char(10) 

      SELECT ''-- Text of the Trigger''= CASE lineid 
                                         WHEN 1 THEN ''GO'' + Char(13) + Char( 
                                                     10) 
                                                     + 
                                                     linetext 
                                         WHEN @MaxID THEN linetext + ''GO'' 
                                         ELSE linetext 
                                       END 
      FROM   @TrigText 
      WHERE  objid = @ObjID 
      ORDER  BY lineid 

      PRINT ''Setuser'' 

      FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID 
  END 

CLOSE ms_users 

PRINT ''GO'' 

PRINT ''------End ------'' 

DEALLOCATE ms_crs_syscom 

DEALLOCATE ms_users 

SET nocount ON 

DECLARE @return_value INT 

Cómo ejecutarlo:

EXEC @return_value = [dbo].[Createscriptofalltriggers] 
  @InclDrop = 1 

SELECT ''Return Value'' = @return_value 

go