Trigger ON Table which fire INSERT into another table which has NOT NULL constraint












0














CREATE TRIGGER logaction ON temployeelog
AFTER INSERT
AS
BEGIN
INSERT INTO TABLE temployee(ename, experience)
SELECT ename,experience FROM INSERTED
END


The structure of temployee



CREATE TABLE temployee
(
ename VARCHAR(20),
experience INT NOT NULL
)

ALTER TABLE temployeeADD DEFAULT (0) FOR experience


When I don't pass data in the experience column WHILE INSERT I get an error.




Cannot insert the value NULL into column 'experience', table
'temployee'; column does not allow nulls. INSERT fails. The statement
has been terminated.




I wanted to pass NULL Values temployeelog table AND wanted those situation to be handled by 'DEFAULT VALUES kept in temployee'



How can I achieve that?










share|improve this question





























    0














    CREATE TRIGGER logaction ON temployeelog
    AFTER INSERT
    AS
    BEGIN
    INSERT INTO TABLE temployee(ename, experience)
    SELECT ename,experience FROM INSERTED
    END


    The structure of temployee



    CREATE TABLE temployee
    (
    ename VARCHAR(20),
    experience INT NOT NULL
    )

    ALTER TABLE temployeeADD DEFAULT (0) FOR experience


    When I don't pass data in the experience column WHILE INSERT I get an error.




    Cannot insert the value NULL into column 'experience', table
    'temployee'; column does not allow nulls. INSERT fails. The statement
    has been terminated.




    I wanted to pass NULL Values temployeelog table AND wanted those situation to be handled by 'DEFAULT VALUES kept in temployee'



    How can I achieve that?










    share|improve this question



























      0












      0








      0







      CREATE TRIGGER logaction ON temployeelog
      AFTER INSERT
      AS
      BEGIN
      INSERT INTO TABLE temployee(ename, experience)
      SELECT ename,experience FROM INSERTED
      END


      The structure of temployee



      CREATE TABLE temployee
      (
      ename VARCHAR(20),
      experience INT NOT NULL
      )

      ALTER TABLE temployeeADD DEFAULT (0) FOR experience


      When I don't pass data in the experience column WHILE INSERT I get an error.




      Cannot insert the value NULL into column 'experience', table
      'temployee'; column does not allow nulls. INSERT fails. The statement
      has been terminated.




      I wanted to pass NULL Values temployeelog table AND wanted those situation to be handled by 'DEFAULT VALUES kept in temployee'



      How can I achieve that?










      share|improve this question















      CREATE TRIGGER logaction ON temployeelog
      AFTER INSERT
      AS
      BEGIN
      INSERT INTO TABLE temployee(ename, experience)
      SELECT ename,experience FROM INSERTED
      END


      The structure of temployee



      CREATE TABLE temployee
      (
      ename VARCHAR(20),
      experience INT NOT NULL
      )

      ALTER TABLE temployeeADD DEFAULT (0) FOR experience


      When I don't pass data in the experience column WHILE INSERT I get an error.




      Cannot insert the value NULL into column 'experience', table
      'temployee'; column does not allow nulls. INSERT fails. The statement
      has been terminated.




      I wanted to pass NULL Values temployeelog table AND wanted those situation to be handled by 'DEFAULT VALUES kept in temployee'



      How can I achieve that?







      sql sql-server






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 23 '18 at 1:27









      Dale Burrell

      2,85232247




      2,85232247










      asked Nov 23 '18 at 1:15









      Passiontolearn

      386




      386
























          1 Answer
          1






          active

          oldest

          votes


















          4














          The table default only comes into play if you don't insert it, so split the insert into one which handles a non-null experience and one which handles a null experience



          INSERT INTO TABLE temployee(ename, experience)
          SELECT ename,experience
          FROM INSERTED
          where experience is not null;

          INSERT INTO TABLE temployee(ename)
          SELECT ename
          FROM INSERTED
          where experience is null;





          share|improve this answer





















          • Thank you.It worked
            – Passiontolearn
            Nov 23 '18 at 13:33











          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
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53439620%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









          4














          The table default only comes into play if you don't insert it, so split the insert into one which handles a non-null experience and one which handles a null experience



          INSERT INTO TABLE temployee(ename, experience)
          SELECT ename,experience
          FROM INSERTED
          where experience is not null;

          INSERT INTO TABLE temployee(ename)
          SELECT ename
          FROM INSERTED
          where experience is null;





          share|improve this answer





















          • Thank you.It worked
            – Passiontolearn
            Nov 23 '18 at 13:33
















          4














          The table default only comes into play if you don't insert it, so split the insert into one which handles a non-null experience and one which handles a null experience



          INSERT INTO TABLE temployee(ename, experience)
          SELECT ename,experience
          FROM INSERTED
          where experience is not null;

          INSERT INTO TABLE temployee(ename)
          SELECT ename
          FROM INSERTED
          where experience is null;





          share|improve this answer





















          • Thank you.It worked
            – Passiontolearn
            Nov 23 '18 at 13:33














          4












          4








          4






          The table default only comes into play if you don't insert it, so split the insert into one which handles a non-null experience and one which handles a null experience



          INSERT INTO TABLE temployee(ename, experience)
          SELECT ename,experience
          FROM INSERTED
          where experience is not null;

          INSERT INTO TABLE temployee(ename)
          SELECT ename
          FROM INSERTED
          where experience is null;





          share|improve this answer












          The table default only comes into play if you don't insert it, so split the insert into one which handles a non-null experience and one which handles a null experience



          INSERT INTO TABLE temployee(ename, experience)
          SELECT ename,experience
          FROM INSERTED
          where experience is not null;

          INSERT INTO TABLE temployee(ename)
          SELECT ename
          FROM INSERTED
          where experience is null;






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 23 '18 at 1:18









          Dale Burrell

          2,85232247




          2,85232247












          • Thank you.It worked
            – Passiontolearn
            Nov 23 '18 at 13:33


















          • Thank you.It worked
            – Passiontolearn
            Nov 23 '18 at 13:33
















          Thank you.It worked
          – Passiontolearn
          Nov 23 '18 at 13:33




          Thank you.It worked
          – Passiontolearn
          Nov 23 '18 at 13:33


















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53439620%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





















































          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







          Popular posts from this blog

          Berounka

          Sphinx de Gizeh

          Different font size/position of beamer's navigation symbols template's content depending on regular/plain...