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