触发将表插入到另一个具有NOT NULL约束的表中的ON表 [英] 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

雇员的结构

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.

无法将值NULL插入表的"experience"列中'临时雇员';列不允许为空.INSERT失败.该声明已终止.

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

我想传递NULL值temployeelog表，并希望通过保留在temployee中的DEFAULT VALUES"来处理这些情况

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

我该如何实现?

推荐答案

仅在不插入表默认值的情况下起作用，因此将插入内容拆分为一个可处理 non-null 体验和处理 null 体验

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;

这篇关于触发将表插入到另一个具有NOT NULL约束的表中的ON表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！