DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01031 when creating a trigger

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    ORA-01031 when creating a trigger

    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?

    Code:
     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"

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You have CREATE TRIGGER privs, right?
    Jeff Hunter

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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!!!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Maybe you should revoke "CREATE PUBLIC SYNONYM" instead of "CREATE TRIGGER"!!!
    Jeff Hunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width