ORA-01031 when creating a trigger

[pando]

Hi

I am puzzled, I own this table however when I create a trigger on the table I get insufficient privileges. Anyone throw some lights?

 CREATE TRIGGER SDE.SPCOL_DEL_CASCADE_40
            AFTER INSERT OR DELETE OR UPDATE OF shape ON SGDP_RUTAS_G
            FOR EACH ROW
 DECLARE
              feature NUMBER;
              sqlstmt VARCHAR2(256);
              inv_spatial_col1 EXCEPTION;
              inv_spatial_col2 EXCEPTION;
            CURSOR select_fid (new_fid NUMBER) IS
                   SELECT SDE.F40.fid
                   FROM SDE.F40
                   WHERE new_fid = SDE.F40.fid;
            BEGIN
            IF DELETING THEN
            DELETE FROM SDE.F40 WHERE SDE.F40.fid = :old.shape;
     DELETE FROM SDE.S40 WHERE SDE.S40.sp_fid = :old.shape
 ;
            END IF;
           IF INSERTING AND (:new.shape IS NOT NULL) THEN
           OPEN select_fid (:new.shape);
                            FETCH select_fid INTO feature;
                            IF select_fid%NOTFOUND THEN
                              RAISE inv_spatial_col2;
                            ELSE
                              CLOSE select_fid;
                            END IF;
 
           END IF;
 
           IF UPDATING AND (:new.shape != :old.shape AND :old.shape IS NOT NULL) THEN
             RAISE inv_spatial_col1;
           END IF;
           IF UPDATING AND (:new.shape IS NULL AND :old.shape IS NOT NULL) THEN
           DELETE FROM SDE.F40 WHERE SDE.F40.fid = :old.shape;
     DELETE FROM SDE.S40 WHERE SDE.S40.sp_fid = :old.shape
 ;
           END IF;
 
            IF UPDATING AND (:old.shape IS NULL AND :new.shape IS NOT NULL) THEN
           OPEN select_fid (:new.shape);
                            FETCH select_fid INTO feature;
                            IF select_fid%NOTFOUND THEN
                              RAISE inv_spatial_col2;
                            ELSE
                              CLOSE select_fid;
                            END IF;
 
           END IF;
 
           EXCEPTION
             WHEN inv_spatial_col1 THEN
             raise_application_error (-20013,'Invalid SDE spatial column UPDATE. Cannot update spatial column value '||TO_CHAR(:new.shape)||' to non-NULL value.');
             WHEN inv_spatial_col2 THEN
             raise_application_error (-20014,'SDE spatial column integrity error. Spatial column value '||TO_CHAR(:new.shape)||' does not exist in SDE feature table SDE.F40.');
 END;
/

ERROR en línea 2:
ORA-01031: privilegios insuficientes


select * from dba_objects where object_name='SGDP_RUTAS_G';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S
-------- -------- ------------------- ------- - - -
SDE
SGDP_RUTAS_G
                                     9729           9729 TABLE
28/06/05 28/06/05 2005-06-28:17:21:26 VALID   N N N

show user
USER es "SDE"

[marist89]

You have CREATE TRIGGER privs, right?

[pando]

I think I am gonna kill the developers.

The problem is this, the owner hasnt got CREATE TRIGGER privs however the owner owns 50 triggers (that part is what puzzles me). It ends up that all tables they use in triggers has public synonyms (or all prefixed with owner name such as owner.table) and all triggers are created by system by running create triggers owner.trg_name (oh my god), since one public synonym was missing when I import the schema one trigger couldnt be compiled so I took the create trigger code and ran in sqlplus as the owner, obviously I should have run the SQL as system!

OMG!!!

[marist89]

Maybe you should revoke "CREATE PUBLIC SYNONYM" instead of "CREATE TRIGGER"!!!