I am trying to replicate a T-SQL trigger in PL/SQL via the APEX SQL Workshop. However, I get a "ORA-24344: success with compilation error" when I run the script.
In T-SQL: In SQL Server, I have the following "bulk" trigger (not for each row). It looks at the "deleted" records, which should correspond to the "old" records in PL/SQL. If the record is a "system" record or if it is not "deletable", then it should rollback that delete action. This script works and the delete action works as expected.
CREATE TRIGGER [Owner].[tblFieldNameOnDelete] ON [Owner].[tblFieldName] AFTER DELETE AS
BEGIN
--Prevent Deletes That Are Not Allowed
IF (SELECT COUNT(FieldNameAID) FROM deleted WHERE deleted.FieldNameIsDeletable=0 Or deleted.FieldNameIsSystem=1)>0
BEGIN
ROLLBACK
RETURN
END
END
In PL-SQL: When I create a simple rollback action, with no reference to the OLD or NEW results, it creates perfectly and acts perfectly (if not usefully). In this case, every attempt to delete a record is rolled back.
CREATE OR REPLACE EDITIONABLE TRIGGER "tblFieldNameOnDelete" AFTER delete on "tblFieldName" FOR EACH ROW
BEGIN
BEGIN
ROLLBACK;
END;
END;
In PL-SQL: When I create anything more complicated, like referencing the OLD result set, it saves the trigger, but generates a (ORA-24344: success with compilation error) error. It doesn't work. The syntax below may not be the most efficient, but is done this way to illustrate the relatively simple method that I am using. Get the IsSystem and IsDeletable properties from the record being deleted.
CREATE OR REPLACE EDITIONABLE TRIGGER "tblFieldNameOnDelete" AFTER delete on "tblFieldName" FOR EACH ROW
BEGIN
DECLARE
vIsDeletable NUMBER := 0;
vIsSystem NUMBER := 0;
BEGIN
--Get The System Setting
SELECT :OLD.FieldNameIsDeletable INTO vIsDeletable FROM :OLD;
SELECT :OLD.FieldNameIsSystem INTO vIsSystem FROM :OLD;
--If Not Deletable or System
IF (vIsDeletable=0 OR vIsSystem=1) THEN
ROLLBACK;
END IF;
END;
END;
In PL/SQL: I have also tried directly referencing the old results, as shown in various examples, with the same error.
CREATE OR REPLACE TRIGGER "tblFieldNameOnDelete" AFTER DELETE on "tblFieldName" FOR EACH ROW
BEGIN
DECLARE
vIsDeletable NUMBER := 0;
vIsSystem NUMBER := 0;
BEGIN
--Get The System Setting
vIsSystem := :old.FieldNameIsSystem;
vIsDeletable := :old.FieldNameIsDeletable;
--If Not Deletable or System
IF (vIsDeletable=0 OR vIsSystem=1) THEN
ROLLBACK;
END IF;
END;
END;
Any ideas?
Rob
Per Cookie's comments, I removed the quotation marks in my CREATE TABLE script and everything worked.