| Trigger not working [message #325975] |
Mon, 09 June 2008 14:48  |
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 #325982 is a reply to message #325975] |
Mon, 09 June 2008 15:06  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #326130 is a reply to message #325985] |
Tue, 10 June 2008 05:05 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
