Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not working (Oracle, 9i)
Trigger not working [message #325975] Mon, 09 June 2008 14:48 Go to next message
Anthemoney
Messages: 2
Registered: June 2008
Junior Member
I’m having trouble getting my newly implemented trigger to work. It simply duplicates values that are inserted in SOURCE.MSSUBPREF into the destination table of VPORTAL.JOBQUEUESUBPREF. However, the destination table is still empty after insertions into the source table. I made sure that the schema where the trigger is created to have insert privileges to the destination table. I also made sure that the values being inserted were unique so as not to violate the primary key constraint. Does anyone have any ideas? Thanks in advance for any help.

Below are the DDL for the trigger and the destination table.

CREATE TRIGGER tbi1_ncs4_mstrsubpref
AFTER INSERT ON SOURCE.MSSUBPREF
FOR EACH ROW
BEGIN
INSERT INTO VPORTAL.JOBQUEUESUBPREF
(MR_SUB_GUID, MR_QUES_OBJ_ID,
MR_PREF_OBJ_ID)
VALUES
(:new.MR_SUB_GUID, :new.MR_QUES_OBJ_ID, :new.MR_PREF_OBJ_ID);
commit;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;




CREATE TABLE VPORTAL.JOBQUEUESUBPREF
( MR_SUB_GUID CHAR(32 BYTE) NOT NULL ENABLE,
MR_QUES_OBJ_ID CHAR(32 BYTE) NOT NULL ENABLE,
MR_PREF_OBJ_ID CHAR(32 BYTE) NOT NULL ENABLE,
PRIMARY KEY (MR_SUB_GUID, MR_QUES_OBJ_ID)
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255
);

CREATE INDEX VPORTAL.JOBQUEUESUBPREF_IE3 ON VPORTAL.JOBQUEUESUBPREF (MR_PREF_OBJ_ID, MR_QUES_OBJ_ID);

CREATE INDEX VPORTAL.JOBQUEUESUBPREF_IE1 ON VPORTAL.JOBQUEUESUBPREF (MR_SUB_GUID);


CREATE INDEX VPORTAL.JOBQUEUESUBPREF_IE4 ON VPORTAL.JOBQUEUESUBPREF (MR_QUES_OBJ_ID);


CREATE INDEX VPORTAL.JOBQUEUESUBPREF_IE2 ON VPORTAL.JOBQUEUESUBPREF (MR_PREF_OBJ_ID);
Re: Trigger not working [message #325978 is a reply to message #325975] Mon, 09 June 2008 14:55 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
you cannot commit in a trigger
beacuse of your exception handling this error does not show up
(when others then null; ignores all errors)
Re: Trigger not working [message #325982 is a reply to message #325975] Mon, 09 June 2008 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, the destination table is still empty after insertions into the source table. I made sure that the schema where the trigger is created to have insert privileges to the destination table

I don't how you could see that with the code you posted as it does not work and returns an error.

As already been said: remove the commit from your trigger.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
And always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Mon, 09 June 2008 15:06]

Report message to a moderator

Re: Trigger not working [message #325985 is a reply to message #325982] Mon, 09 June 2008 15:23 Go to previous messageGo to next message
Anthemoney
Messages: 2
Registered: June 2008
Junior Member
That's unusual because my other triggers all use commit and work fine. I will try this out though and see if it works. Thanks for the input.
Re: Trigger not working [message #325986 is a reply to message #325975] Mon, 09 June 2008 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>That's unusual because my other triggers all use commit and work fine>
Please post proof.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Trigger not working [message #326044 is a reply to message #325985] Tue, 10 June 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't if it is true or not but it is surely a very bad thing.
The only case you have to commit inside a trigger is for logging purpose, all the other cases are errors.

Regards
Michel
Re: Trigger not working [message #326130 is a reply to message #325985] Tue, 10 June 2008 05:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's unusual because my other triggers all use commit and work fine

No they don't.

The only way you can get a commit into a transactional trigger is to have the trigger performing an autonomous transaction, and there are almost no circumstances where you'd want to do that.
Previous Topic: how to find whether the table is inserted or not in a procedure
Next Topic: INSERT using ROWNUM
Goto Forum:
  


Current Time: Sat Dec 10 03:29:37 CST 2016

Total time taken to generate the page: 0.07436 seconds