Mixing
Simple Trigger Error?! PL/SQL: ORA-00933:
I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.
I have re-jigged the trigger a lot but cannot seem to bypass the compilation error! I assume it's something small?
DROP TABLE MyAuditTable;
CREATE TABLE MyAuditTable (
audit_id INTEGER NOT NULL,
new_name VARCHAR2 (30),
new_postcode VARCHAR2 (20),
status VARCHAR2 (15),
CONSTRAINT pk_MyAuditTable PRIMARY KEY ( audit_id )
);
DROP sequence MySeq;
Create sequence MySeq MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1;
drop trigger MyTrigger;
create trigger MyTrigger
after insert on my_consultant_table
for each row
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
/
ERROR: PL/SQL: ORA-00933:
So the audit table should now have the newly inputted data from the consultant table, which contains name and postcode attributes. Another trigger will fire so that when the status is changed, these changes are permitted.
Thank You!
php oracle plsql triggers ora-00933
edited Jan 2 at 14:19
GMB
18.8k31028
asked Jan 2 at 14:13
JazzyBJazzyB
1
add a comment |
I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.
I have re-jigged the trigger a lot but cannot seem to bypass the compilation error! I assume it's something small?
DROP TABLE MyAuditTable;
CREATE TABLE MyAuditTable (
audit_id INTEGER NOT NULL,
new_name VARCHAR2 (30),
new_postcode VARCHAR2 (20),
status VARCHAR2 (15),
CONSTRAINT pk_MyAuditTable PRIMARY KEY ( audit_id )
);
DROP sequence MySeq;
Create sequence MySeq MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1;
drop trigger MyTrigger;
create trigger MyTrigger
after insert on my_consultant_table
for each row
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
/
ERROR: PL/SQL: ORA-00933:
So the audit table should now have the newly inputted data from the consultant table, which contains name and postcode attributes. Another trigger will fire so that when the status is changed, these changes are permitted.
Thank You!
php oracle plsql triggers ora-00933
edited Jan 2 at 14:19
GMB
18.8k31028
asked Jan 2 at 14:13
JazzyBJazzyB
1
2
Just removefrom my_consultant_table
– Kaushik Nayak
Jan 2 at 14:21
add a comment |
I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.
I have re-jigged the trigger a lot but cannot seem to bypass the compilation error! I assume it's something small?
DROP TABLE MyAuditTable;
CREATE TABLE MyAuditTable (
audit_id INTEGER NOT NULL,
new_name VARCHAR2 (30),
new_postcode VARCHAR2 (20),
status VARCHAR2 (15),
CONSTRAINT pk_MyAuditTable PRIMARY KEY ( audit_id )
);
DROP sequence MySeq;
Create sequence MySeq MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1;
drop trigger MyTrigger;
create trigger MyTrigger
after insert on my_consultant_table
for each row
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
/
ERROR: PL/SQL: ORA-00933:
So the audit table should now have the newly inputted data from the consultant table, which contains name and postcode attributes. Another trigger will fire so that when the status is changed, these changes are permitted.
Thank You!
php oracle plsql triggers ora-00933
edited Jan 2 at 14:19
GMB
18.8k31028
asked Jan 2 at 14:13
JazzyBJazzyB
1
I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.
I have re-jigged the trigger a lot but cannot seem to bypass the compilation error! I assume it's something small?
DROP TABLE MyAuditTable;
CREATE TABLE MyAuditTable (
audit_id INTEGER NOT NULL,
new_name VARCHAR2 (30),
new_postcode VARCHAR2 (20),
status VARCHAR2 (15),
CONSTRAINT pk_MyAuditTable PRIMARY KEY ( audit_id )
);
DROP sequence MySeq;
Create sequence MySeq MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1;
drop trigger MyTrigger;
create trigger MyTrigger
after insert on my_consultant_table
for each row
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
/
ERROR: PL/SQL: ORA-00933:
So the audit table should now have the newly inputted data from the consultant table, which contains name and postcode attributes. Another trigger will fire so that when the status is changed, these changes are permitted.
Thank You!
php oracle plsql triggers ora-00933
php oracle plsql triggers ora-00933
edited Jan 2 at 14:19
GMB
18.8k31028
asked Jan 2 at 14:13
JazzyBJazzyB
1
edited Jan 2 at 14:19
GMB
18.8k31028
asked Jan 2 at 14:13
JazzyBJazzyB
1
edited Jan 2 at 14:19
GMB
18.8k31028
edited Jan 2 at 14:19
GMB
18.8k31028
edited Jan 2 at 14:19
GMB
18.8k31028
18.8k31028
asked Jan 2 at 14:13
JazzyBJazzyB
1
asked Jan 2 at 14:13
JazzyBJazzyB
1
asked Jan 2 at 14:13
JazzyBJazzyB
1
1
2
Just removefrom my_consultant_table
– Kaushik Nayak
Jan 2 at 14:21
add a comment |
2
Just removefrom my_consultant_table
– Kaushik Nayak
Jan 2 at 14:21
2
2
Just remove
from my_consultant_table– Kaushik Nayak
Jan 2 at 14:21
Just remove
from my_consultant_table– Kaushik Nayak
Jan 2 at 14:21
add a comment |
2 Answers
2
active
oldest
votes
The line
from my_consultant_table
Is unnecessary.
Should be:
insert into MyAuditTable values (MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending');
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
add a comment |
This ORA-00933 denotes a syntax error in your trigger declaration :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
The trailing from my_consultant_table does not make sense, just remove it and you should be fine :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
);
end;
answered Jan 2 at 14:25
GMBGMB
18.8k31028
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
1
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007887%2fsimple-trigger-error-pl-sql-ora-00933%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The line
from my_consultant_table
Is unnecessary.
Should be:
insert into MyAuditTable values (MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending');
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
add a comment |
The line
from my_consultant_table
Is unnecessary.
Should be:
insert into MyAuditTable values (MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending');
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
add a comment |
The line
from my_consultant_table
Is unnecessary.
Should be:
insert into MyAuditTable values (MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending');
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
The line
from my_consultant_table
Is unnecessary.
Should be:
insert into MyAuditTable values (MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending');
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
answered Jan 2 at 14:25
Virgil IonescuVirgil Ionescu
31738
31738
add a comment |
add a comment |
This ORA-00933 denotes a syntax error in your trigger declaration :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
The trailing from my_consultant_table does not make sense, just remove it and you should be fine :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
);
end;
answered Jan 2 at 14:25
GMBGMB
18.8k31028
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
1
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
add a comment |
This ORA-00933 denotes a syntax error in your trigger declaration :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
The trailing from my_consultant_table does not make sense, just remove it and you should be fine :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
);
end;
answered Jan 2 at 14:25
GMBGMB
18.8k31028
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
1
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
add a comment |
This ORA-00933 denotes a syntax error in your trigger declaration :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
The trailing from my_consultant_table does not make sense, just remove it and you should be fine :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
);
end;
answered Jan 2 at 14:25
GMBGMB
18.8k31028
This ORA-00933 denotes a syntax error in your trigger declaration :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
)
from my_consultant_table;
end;
The trailing from my_consultant_table does not make sense, just remove it and you should be fine :
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending'
);
end;
answered Jan 2 at 14:25
GMBGMB
18.8k31028
answered Jan 2 at 14:25
GMBGMB
18.8k31028
answered Jan 2 at 14:25
GMBGMB
18.8k31028
answered Jan 2 at 14:25
GMBGMB
18.8k31028
18.8k31028
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
1
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
add a comment |
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
1
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
Ah perfect! I assumed I had to state the table name. The trigger runs perfectly but no data is being inserted into the audit table?
– JazzyB
Jan 2 at 14:33
1
1
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
@JazzyB : it should get inserted... did you commit your transaction ? Can you see the inserted data in my_consultant_table ?
– GMB
Jan 2 at 14:39
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
I have received the following error: failed to parse SQL query: ORA-00904: "PERMISSIONS": invalid identifier.. I have a permissions column on the end of my consultant table so that a consultant can only view their own data using UPPER APP_USER (which works fine)
– JazzyB
Jan 2 at 14:44
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
yes the changed were made in the consultant table
– JazzyB
Jan 2 at 14:47
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007887%2fsimple-trigger-error-pl-sql-ora-00933%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Just remove
from my_consultant_table– Kaushik Nayak
Jan 2 at 14:21