How to create a trigger in oracle to prevent insert datesOracle trigger to update columns dailyAudit table...
What is the Guild Die for?
Found a major flaw in paper from home university – to which I would like to return
Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?
Uncountable set with a non-discrete metric
I hate taking lectures, can I still survive in academia?
QGIS 3.4.4 sorts numerical values as text, solution?
"Happy is as happy does" What does this mean?
Apparently I’m calling random numbers but there's nothing in the call log
Can "ee" appear in Latin?
Calculating solubility of iron(III) hydroxide in water
Get category id
Badly designed reimbursement form. What does that say about the company?
'Dimension too large' error when setmathfont has the option Scale=MatchLowercase
How to create a custom script to force the download of a .csv file that is generated in real time?
Run a command that requires sudo after a time has passed
Can a planet be tidally unlocked?
How do I write a maintainable, fast, compile-time bit-mask in C++?
Rudeness by being polite
How can a kingdom keep the secret of a missing monarch from the public?
Manager has noticed coworker's excessive breaks. Should I warn him?
What prevents people from lying about where they live in order to reduce state income taxes?
Where can I educate myself on DnD universe lore, specifically on vampires and supernatural monsters?
Ramanujan's radical and how we define an infinite nested radical
Movie where a woman is running away from a plant creature which rips her shorts?
How to create a trigger in oracle to prevent insert dates
Oracle trigger to update columns dailyAudit table insert restrictionTable design for multi-type datatrigger or check constraint insert values based upon existence of othersMoving a date between tables is causing error ORA-01841Oracle - create changelog querySQL Error: ORA-14300: While partitioning and subpartitioningHow to commit transaction on an after update event trigger?PL/SQL triggers with two existing tables and one empty tableUsing Oracle XE, how do I create a CASE statement To return a specific date
I'm learning Oracle and I had a problem.
Chat table:
CREATE TABLE training
(
id_training NUMBER,
id_user NUMBER,
start_training DATE,
end_training DATE
);
I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.
I was thinking of using .old and .new in the triggere, but if I use an insert in my trigger the data of .old always will be null.
How can I overcome this problem of changing .old values, if I'm inserting new values inside my trigger?
oracle oracle-11g oracle-sql-developer date
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
asked Aug 2 '18 at 6:06
StalinnStalinn
1
bumped to the homepage by Community♦ 22 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm learning Oracle and I had a problem.
Chat table:
CREATE TABLE training
(
id_training NUMBER,
id_user NUMBER,
start_training DATE,
end_training DATE
);
I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.
I was thinking of using .old and .new in the triggere, but if I use an insert in my trigger the data of .old always will be null.
How can I overcome this problem of changing .old values, if I'm inserting new values inside my trigger?
oracle oracle-11g oracle-sql-developer date
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
asked Aug 2 '18 at 6:06
StalinnStalinn
1
bumped to the homepage by Community♦ 22 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Do it by proper WHERE NOT EXISTS condition in INSERT query, do not use trigger.
– Akina
Aug 2 '18 at 6:31
ensure that the dates will not be located between any previously registered start_training and end_training dates. Any - including 'in different records'? Or exclude overlapping only?
– Akina
Aug 2 '18 at 6:35
@Akina It's my example: I have one user with 01-01-18 like start_training and 05-01-18 like end_training, now I want to register the same user but this time with 03-01-18 like star_training and 06-01-18, these dates are different to the dates of the first register, but 03-01-18 is included between the dates of the first register
– Stalinn
Aug 2 '18 at 13:48
add a comment |
I'm learning Oracle and I had a problem.
Chat table:
CREATE TABLE training
(
id_training NUMBER,
id_user NUMBER,
start_training DATE,
end_training DATE
);
I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.
I was thinking of using .old and .new in the triggere, but if I use an insert in my trigger the data of .old always will be null.
How can I overcome this problem of changing .old values, if I'm inserting new values inside my trigger?
oracle oracle-11g oracle-sql-developer date
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
asked Aug 2 '18 at 6:06
StalinnStalinn
1
I'm learning Oracle and I had a problem.
Chat table:
CREATE TABLE training
(
id_training NUMBER,
id_user NUMBER,
start_training DATE,
end_training DATE
);
I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.
I was thinking of using .old and .new in the triggere, but if I use an insert in my trigger the data of .old always will be null.
How can I overcome this problem of changing .old values, if I'm inserting new values inside my trigger?
oracle oracle-11g oracle-sql-developer date
oracle oracle-11g oracle-sql-developer date
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
asked Aug 2 '18 at 6:06
StalinnStalinn
1
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
asked Aug 2 '18 at 6:06
StalinnStalinn
1
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
edited Aug 2 '18 at 8:10
Husam Mohamed
4181313
4181313
asked Aug 2 '18 at 6:06
StalinnStalinn
1
asked Aug 2 '18 at 6:06
StalinnStalinn
1
asked Aug 2 '18 at 6:06
StalinnStalinn
1
1
bumped to the homepage by Community♦ 22 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 22 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Do it by proper WHERE NOT EXISTS condition in INSERT query, do not use trigger.
– Akina
Aug 2 '18 at 6:31
ensure that the dates will not be located between any previously registered start_training and end_training dates. Any - including 'in different records'? Or exclude overlapping only?
– Akina
Aug 2 '18 at 6:35
@Akina It's my example: I have one user with 01-01-18 like start_training and 05-01-18 like end_training, now I want to register the same user but this time with 03-01-18 like star_training and 06-01-18, these dates are different to the dates of the first register, but 03-01-18 is included between the dates of the first register
– Stalinn
Aug 2 '18 at 13:48
add a comment |
Do it by proper WHERE NOT EXISTS condition in INSERT query, do not use trigger.
– Akina
Aug 2 '18 at 6:31
ensure that the dates will not be located between any previously registered start_training and end_training dates. Any - including 'in different records'? Or exclude overlapping only?
– Akina
Aug 2 '18 at 6:35
@Akina It's my example: I have one user with 01-01-18 like start_training and 05-01-18 like end_training, now I want to register the same user but this time with 03-01-18 like star_training and 06-01-18, these dates are different to the dates of the first register, but 03-01-18 is included between the dates of the first register
– Stalinn
Aug 2 '18 at 13:48
Do it by proper WHERE NOT EXISTS condition in INSERT query, do not use trigger.
– Akina
Aug 2 '18 at 6:31
Do it by proper WHERE NOT EXISTS condition in INSERT query, do not use trigger.
– Akina
Aug 2 '18 at 6:31
ensure that the dates will not be located between any previously registered start_training and end_training dates. Any - including 'in different records'? Or exclude overlapping only?
– Akina
Aug 2 '18 at 6:35
ensure that the dates will not be located between any previously registered start_training and end_training dates. Any - including 'in different records'? Or exclude overlapping only?
– Akina
Aug 2 '18 at 6:35
@Akina It's my example: I have one user with 01-01-18 like start_training and 05-01-18 like end_training, now I want to register the same user but this time with 03-01-18 like star_training and 06-01-18, these dates are different to the dates of the first register, but 03-01-18 is included between the dates of the first register
– Stalinn
Aug 2 '18 at 13:48
@Akina It's my example: I have one user with 01-01-18 like start_training and 05-01-18 like end_training, now I want to register the same user but this time with 03-01-18 like star_training and 06-01-18, these dates are different to the dates of the first register, but 03-01-18 is included between the dates of the first register
– Stalinn
Aug 2 '18 at 13:48
add a comment |
1 Answer
1
active
oldest
votes
Even if you could change the .old and/or .new values in the trigger, what would you change them to?
Presumably the User will have chosen a training event to attend based on their availability. Having the database "magically" change it to some other event when they hit "Save" doesn't make for a User-Friendly experience.
I would argue the best you could do would be to raise an error, throw out the whole transaction and let them pick another event.
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f213869%2fhow-to-create-a-trigger-in-oracle-to-prevent-insert-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Even if you could change the .old and/or .new values in the trigger, what would you change them to?
Presumably the User will have chosen a training event to attend based on their availability. Having the database "magically" change it to some other event when they hit "Save" doesn't make for a User-Friendly experience.
I would argue the best you could do would be to raise an error, throw out the whole transaction and let them pick another event.
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
add a comment |
Even if you could change the .old and/or .new values in the trigger, what would you change them to?
Presumably the User will have chosen a training event to attend based on their availability. Having the database "magically" change it to some other event when they hit "Save" doesn't make for a User-Friendly experience.
I would argue the best you could do would be to raise an error, throw out the whole transaction and let them pick another event.
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
add a comment |
Even if you could change the .old and/or .new values in the trigger, what would you change them to?
Presumably the User will have chosen a training event to attend based on their availability. Having the database "magically" change it to some other event when they hit "Save" doesn't make for a User-Friendly experience.
I would argue the best you could do would be to raise an error, throw out the whole transaction and let them pick another event.
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
Even if you could change the .old and/or .new values in the trigger, what would you change them to?
Presumably the User will have chosen a training event to attend based on their availability. Having the database "magically" change it to some other event when they hit "Save" doesn't make for a User-Friendly experience.
I would argue the best you could do would be to raise an error, throw out the whole transaction and let them pick another event.
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
answered Aug 2 '18 at 10:29
Phill W.Phill W.
79633
79633
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f213869%2fhow-to-create-a-trigger-in-oracle-to-prevent-insert-dates%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
Do it by proper WHERE NOT EXISTS condition in INSERT query, do not use trigger.
– Akina
Aug 2 '18 at 6:31
ensure that the dates will not be located between any previously registered start_training and end_training dates. Any - including 'in different records'? Or exclude overlapping only?
– Akina
Aug 2 '18 at 6:35
@Akina It's my example: I have one user with 01-01-18 like start_training and 05-01-18 like end_training, now I want to register the same user but this time with 03-01-18 like star_training and 06-01-18, these dates are different to the dates of the first register, but 03-01-18 is included between the dates of the first register
– Stalinn
Aug 2 '18 at 13:48