-
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"
-
You have CREATE TRIGGER privs, right?
Jeff Hunter
-
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!!!
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|