CODEWITHSUNDEEP
sqlsql-servertriggers
Passiontolearn
Passiontolearn

Reputation: 103

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

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?

Upvotes: 0

Views: 62

Answers (1)

Dale K
Dale K

Reputation: 27225

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;

Upvotes: 4

Related Questions