Trigger ON Table which fire INSERT into another table which has NOT NULL constraint , which has float...
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,marks FROM INSERTED
END
The structure of tstudent
CREATE TABLE tstudent
(
name VARCHAR(20),
marks FLOAT NOT NULL
)
ALTER TABLE tstudent ADD DEFAULT (0) FOR marks
When I don't pass data in the marks column WHILE INSERTING record into tstudentlog
I get an error:
Cannot insert the value NULL into column 'marks', table 'tstudent'; column does not allow nulls. INSERT fails. The statement has been terminated.
I tried the following but did not work
INSERT INTO TABLE tstudent(sname)
SELECT sname
FROM INSERTED
where marks is null;
I wanted to pass NULL Values tstudent table AND wanted those situdation to be handled by 'DEFAULT VALUES kept in tstudent'
How can I achieve that?
sql-server tsql database-trigger edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
asked Nov 23 '18 at 13:40
Passiontolearn
386
|
show 3 more comments
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,marks FROM INSERTED
END
The structure of tstudent
CREATE TABLE tstudent
(
name VARCHAR(20),
marks FLOAT NOT NULL
)
ALTER TABLE tstudent ADD DEFAULT (0) FOR marks
When I don't pass data in the marks column WHILE INSERTING record into tstudentlog
I get an error:
Cannot insert the value NULL into column 'marks', table 'tstudent'; column does not allow nulls. INSERT fails. The statement has been terminated.
I tried the following but did not work
INSERT INTO TABLE tstudent(sname)
SELECT sname
FROM INSERTED
where marks is null;
I wanted to pass NULL Values tstudent table AND wanted those situdation to be handled by 'DEFAULT VALUES kept in tstudent'
How can I achieve that?
sql-server tsql database-trigger edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
asked Nov 23 '18 at 13:40
Passiontolearn
386
1
How about doingwhere marks isNOTnull;...
– Peter B
Nov 23 '18 at 13:44
5
So why isn't this a duplicate of your other question asked 12 hours ago?
– Damien_The_Unbeliever
Nov 23 '18 at 13:54
2
Why are you mix and matching datatypes?
– Larnu
Nov 23 '18 at 14:14
1
Am I reading this correct that you create a new student when you log something about a user in tablestudentlog? Should that not be the other way around ? Or am I missing something ?
– GuidoG
Nov 23 '18 at 14:25
1
Possible duplicate of Trigger ON Table which fire INSERT into another table which has NOT NULL constraint
– SMor
Nov 23 '18 at 14:33
|
show 3 more comments
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,marks FROM INSERTED
END
The structure of tstudent
CREATE TABLE tstudent
(
name VARCHAR(20),
marks FLOAT NOT NULL
)
ALTER TABLE tstudent ADD DEFAULT (0) FOR marks
When I don't pass data in the marks column WHILE INSERTING record into tstudentlog
I get an error:
Cannot insert the value NULL into column 'marks', table 'tstudent'; column does not allow nulls. INSERT fails. The statement has been terminated.
I tried the following but did not work
INSERT INTO TABLE tstudent(sname)
SELECT sname
FROM INSERTED
where marks is null;
I wanted to pass NULL Values tstudent table AND wanted those situdation to be handled by 'DEFAULT VALUES kept in tstudent'
How can I achieve that?
sql-server tsql database-trigger edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
asked Nov 23 '18 at 13:40
Passiontolearn
386
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,marks FROM INSERTED
END
The structure of tstudent
CREATE TABLE tstudent
(
name VARCHAR(20),
marks FLOAT NOT NULL
)
ALTER TABLE tstudent ADD DEFAULT (0) FOR marks
When I don't pass data in the marks column WHILE INSERTING record into tstudentlog
I get an error:
Cannot insert the value NULL into column 'marks', table 'tstudent'; column does not allow nulls. INSERT fails. The statement has been terminated.
I tried the following but did not work
INSERT INTO TABLE tstudent(sname)
SELECT sname
FROM INSERTED
where marks is null;
I wanted to pass NULL Values tstudent table AND wanted those situdation to be handled by 'DEFAULT VALUES kept in tstudent'
How can I achieve that?
sql-server tsql database-trigger sql-server tsql database-trigger edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
asked Nov 23 '18 at 13:40
Passiontolearn
386
edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
asked Nov 23 '18 at 13:40
Passiontolearn
386
edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
edited Nov 23 '18 at 13:42
a_horse_with_no_name
292k46445540
292k46445540
asked Nov 23 '18 at 13:40
Passiontolearn
386
asked Nov 23 '18 at 13:40
Passiontolearn
386
asked Nov 23 '18 at 13:40
Passiontolearn
386
386
1
How about doingwhere marks isNOTnull;...
– Peter B
Nov 23 '18 at 13:44
5
So why isn't this a duplicate of your other question asked 12 hours ago?
– Damien_The_Unbeliever
Nov 23 '18 at 13:54
2
Why are you mix and matching datatypes?
– Larnu
Nov 23 '18 at 14:14
1
Am I reading this correct that you create a new student when you log something about a user in tablestudentlog? Should that not be the other way around ? Or am I missing something ?
– GuidoG
Nov 23 '18 at 14:25
1
Possible duplicate of Trigger ON Table which fire INSERT into another table which has NOT NULL constraint
– SMor
Nov 23 '18 at 14:33
|
show 3 more comments
1
How about doingwhere marks isNOTnull;...
– Peter B
Nov 23 '18 at 13:44
5
So why isn't this a duplicate of your other question asked 12 hours ago?
– Damien_The_Unbeliever
Nov 23 '18 at 13:54
2
Why are you mix and matching datatypes?
– Larnu
Nov 23 '18 at 14:14
1
Am I reading this correct that you create a new student when you log something about a user in tablestudentlog? Should that not be the other way around ? Or am I missing something ?
– GuidoG
Nov 23 '18 at 14:25
1
Possible duplicate of Trigger ON Table which fire INSERT into another table which has NOT NULL constraint
– SMor
Nov 23 '18 at 14:33
1
1
How about doing
where marks is NOT null; ...– Peter B
Nov 23 '18 at 13:44
How about doing
where marks is NOT null; ...– Peter B
Nov 23 '18 at 13:44
5
5
So why isn't this a duplicate of your other question asked 12 hours ago?
– Damien_The_Unbeliever
Nov 23 '18 at 13:54
So why isn't this a duplicate of your other question asked 12 hours ago?
– Damien_The_Unbeliever
Nov 23 '18 at 13:54
2
2
Why are you mix and matching datatypes?
– Larnu
Nov 23 '18 at 14:14
Why are you mix and matching datatypes?
– Larnu
Nov 23 '18 at 14:14
1
1
Am I reading this correct that you create a new student when you log something about a user in table
studentlog ? Should that not be the other way around ? Or am I missing something ?– GuidoG
Nov 23 '18 at 14:25
Am I reading this correct that you create a new student when you log something about a user in table
studentlog ? Should that not be the other way around ? Or am I missing something ?– GuidoG
Nov 23 '18 at 14:25
1
1
Possible duplicate of Trigger ON Table which fire INSERT into another table which has NOT NULL constraint
– SMor
Nov 23 '18 at 14:33
Possible duplicate of Trigger ON Table which fire INSERT into another table which has NOT NULL constraint
– SMor
Nov 23 '18 at 14:33
|
show 3 more comments
1 Answer
1
active
oldest
votes
I had a lot of problem with default values in table,
It could go well to use coalesce on insert statement?
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,coalesce(marks,0) FROM INSERTED
END
answered Nov 23 '18 at 13:48
picklerick
33018
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%2f53447794%2ftrigger-on-table-which-fire-insert-into-another-table-which-has-not-null-constra%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
I had a lot of problem with default values in table,
It could go well to use coalesce on insert statement?
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,coalesce(marks,0) FROM INSERTED
END
answered Nov 23 '18 at 13:48
picklerick
33018
add a comment |
I had a lot of problem with default values in table,
It could go well to use coalesce on insert statement?
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,coalesce(marks,0) FROM INSERTED
END
answered Nov 23 '18 at 13:48
picklerick
33018
add a comment |
I had a lot of problem with default values in table,
It could go well to use coalesce on insert statement?
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,coalesce(marks,0) FROM INSERTED
END
answered Nov 23 '18 at 13:48
picklerick
33018
I had a lot of problem with default values in table,
It could go well to use coalesce on insert statement?
CREATE TRIGGER studenttr ON tstudentlog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE tstudent(sname, marks)
SELECT sname,coalesce(marks,0) FROM INSERTED
END
answered Nov 23 '18 at 13:48
picklerick
33018
answered Nov 23 '18 at 13:48
picklerick
33018
answered Nov 23 '18 at 13:48
picklerick
33018
answered Nov 23 '18 at 13:48
picklerick
33018
33018
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53447794%2ftrigger-on-table-which-fire-insert-into-another-table-which-has-not-null-constra%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
1
How about doing
where marks isNOTnull;...– Peter B
Nov 23 '18 at 13:44
5
So why isn't this a duplicate of your other question asked 12 hours ago?
– Damien_The_Unbeliever
Nov 23 '18 at 13:54
2
Why are you mix and matching datatypes?
– Larnu
Nov 23 '18 at 14:14
1
Am I reading this correct that you create a new student when you log something about a user in table
studentlog? Should that not be the other way around ? Or am I missing something ?– GuidoG
Nov 23 '18 at 14:25
1
Possible duplicate of Trigger ON Table which fire INSERT into another table which has NOT NULL constraint
– SMor
Nov 23 '18 at 14:33