-
table error
hi,
REATE TABLE T_RE_SEQU
(
SCHEMA_NAME VARCHAR2(60 CHAR),
SEQUENCE_NAME VARCHAR2(60 CHAR),
NB_INITIAL_VALUE NUMBER(38),
NB_CURRENT_VALUE NUMBER(38),
NB_INCREMENT_VALUE NUMBER(38)
)
Last edited by sushant; 03-17-2008 at 07:49 AM.
-
This should work. You needed to specify for each row in order to access the :NEW. and :OLD. metarows. If you wanted to update a different row in the table during an update, delete or insert, then you would need to insert into a temporary table and have an after statement level trigger fire and read the temporary table and that would be able to update the table without a mutating table error. There are four basic type of triggers there are two before triggers and two after triggers comprised of statement level and row level triggers.
Code:
CREATE OR REPLACE TRIGGER TR_REF_SEQUENCE_UPDATE_DATE
BEFORE INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
FOR EACH ROW
DECLARE
BEGIN
:NEW.DT_LAST_UPDATED = SYSDATE;
END TR_REF_SEQUENCE_UPDATE_DATE;
/
-
hi gandolf989,
Thanks for your reply.this is a good suggestion to use before
trigger to acess :new variables. i have already tried this but as
i mentioned in origional thread i'm getting ORA-00036: maximum
number of recursive SQL levels (50) exceeded Error.
please look at the code bellow.
CREATE OR REPLACE PACKAGE REF_SEQ_PKG
AS
TYPE TYPE_SCHEMA IS TABLE OF U_REF.T_REF_SEQUENCE.VC_SCHEMA_NAME%TYPE INDEX BY BINARY_INTEGER;
TYPE TYPE_SEQUENCE IS TABLE OF U_REF.T_REF_SEQUENCE.VC_SEQUENCE_NAME%TYPE INDEX BY BINARY_INTEGER;
TYPE_SCHEMA_VAR TYPE_SCHEMA ;
TYPE_SCHEMA_EMPTY TYPE_SCHEMA;
TYPE_SEQUENCE_VAR TYPE_SCHEMA ;
TYPE_SEQUENCE_EMPTY TYPE_SCHEMA;
END;
/
CREATE OR REPLACE TRIGGER TR_REF_SEQ_PKG
BEFORE INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
BEGIN
REF_SEQ_PKG.TYPE_SCHEMA_VAR :=
REF_SEQ_PKG.TYPE_SCHEMA_EMPTY ;
REF_SEQ_PKG.TYPE_SEQUENCE_VAR :=
REF_SEQ_PKG.TYPE_SEQUENCE_EMPTY;
END;
/
CREATE OR REPLACE TRIGGER TR_REF_SEQ_PKG_BI_ROW
BEFORE INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
FOR EACH ROW
BEGIN
REF_SEQ_PKG.TYPE_SCHEMA_VAR(REF_SEQ_PKG.TYPE_SCHEMA_VAR.COUNT +1) := :NEW.VC_SCHEMA_NAME ;
REF_SEQ_PKG.TYPE_SEQUENCE_VAR(REF_SEQ_PKG.TYPE_SEQUENCE_VAR.COUNT + 1) := :NEW.VC_SEQUENCE_NAME ;
END;
/
CREATE OR REPLACE TRIGGER TR_T_REF_SEQUENCE_AI
AFTER INSERT OR UPDATE ON U_REF.T_REF_SEQUENCE
BEGIN
DBMS_OUTPUT.PUT_LINE(3);
FOR i IN 1 .. REF_SEQ_PKG.TYPE_SCHEMA_VAR.COUNT
LOOP
UPDATE T_REF_SEQUENCE T1
SET DT_LAST_UPDATED = SYSDATE
WHERE EXISTS (SELECT 1
FROM DUAL
WHERE t1.VC_SCHEMA_NAME = REF_SEQ_PKG.TYPE_SCHEMA_VAR(i)
AND t1.VC_SEQUENCE_NAME = REF_SEQ_PKG.TYPE_SEQUENCE_VAR(i) );
END LOOP;
END;
/
when i try to insert into the same table it gives ORA-00036: maximum
number of recursive SQL levels (50) exceeded Error.
This is because there is recursive call to the trigger.
do you find any bug in code. how to overcome this error?
please suggest any solution.
THANKS
sushant
-
Code:
declare
v_understood := false;
begin
while v_understood == false loop
do read-Galdolf989-post
if understood then
v_understood := true;
end if;
end loop;
do fix-your-code;
end;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Sushant, spend some time reading PL/SQL Best Practices by Steven Feuerstein. It's a short book, but it should help you to understand the PL/SQL language.
Once you do that, writing triggers should be much easier.
-
hi gandolf989,
Thanks for your valuable reply. i will work on it.
can you send me the link of this book ?
Thanks
sushant
-
I'm wondering if the next thing you are gonna do is asking gandolf989 to read it for you. Don't be that lazy and just google "PL/SQL Best Practices by Steven Feuerstein"
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
you have taken it on wrong way. i was just not sure that this Book is
available for all user or not. thats why i requested to share the link only.
i regret for the inconvenience.
Thanks for your help
-
Originally Posted by sushant
you have taken it on wrong way. i was just not sure that this Book is
available for all user or not. thats why i requested to share the link only.
i regret for the inconvenience.
Thanks for your help
http://www.google.com/search?q=%22PL%2FSQL+Best+Practices+by+Steven+Feuerstein%22&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-USfficial&client=firefox-a
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
|