有没有一种方法可以在一个脚本中创建多个触发器？

Question

我正在尝试创建多个触发器，只需将一个脚本上传到 Oracle DB/APEX 工作区并运行一次。

与我尝试使用的脚本相比，这是一个简短的脚本：

    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; 

我尝试将 GO 放在每个单独的块之间，但仍然只创建第一个触发器，然后给我第二个说法的错误：

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

我希望可以做这个。非常感谢您的时间和兴趣=）

Answer 1

每次触发后在新行添加一个正斜杠以执行缓冲区中的命令：

create trigger...
...
end;
/

Answer 2

在每个触发器语句之间的空行上放置斜杠“/”作为第一个字符。这在 SQL*PLUS 中相当于“go”。

Answer 3

是的，我们可以使用 sql 文件内的 FORWARD SLASH / 在单个脚本中执行多个过程/触发器/函数。

如下所示：

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; 

/

然后oracle会认为它是新的语句/块。

Answer 4

放置一个正斜杠

/

在单独一行的两个语句之间

。 Oracle 将接受它作为新语句

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 

如何执行：

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

SELECT 'Return Value' = @return_value 

go