SQL Server由于主外键约束导致插入失败的记录方法

以下代码由PHP站长网 52php.cn网络自互联网

此刻PHP站长网小编把它分享给各人，仅供参考

--建设Department(deptID,deptName)和UserInfo(userID,userName,sex,loginDate,deptid)
--个中deptID,userID是自动增添的,而且是主键。deptid是外键。而且插入测试数据

--建设序列
CREATE SEQUENCE seq_Dep
 START WITH 1
 INCREMENT BY 1
 NOMAXVALUE
 NOCYCLE
 CACHE 30;
--建表Department
CREATE TABLE Department
(
 deptID VARCHAR2(10) PRIMARY KEY,deptName VARCHAR2(20)
);

--插入测试数据
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'人事部');
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'技能部');
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'后勤部');
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'司理部');
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'贩卖部');
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'处事部');
INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'公关部');
COMMIT;

--建设序列
CREATE SEQUENCE seq_User
 START WITH 1
 INCREMENT BY 1
 NOMAXVALUE
 NOCYCLE
 CACHE 30;
--建表UserInfo
CREATE TABLE UserInfo
(
 userID VARCHAR2(10) PRIMARY KEY,userName VARCHAR2(20),sex VARCHAR2(2),loginDate DATE,deptid VARCHAR2(10) REFERENCES Department (deptID)
);

--插入测试数据
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Eric Schmidt','男','12-9月-07','D1');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Larry Page','12-10月-07','D3');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Sergey Brin','12-11月-07','D5');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'W. M. Coughran,Jr.','1-12月-07','D4');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'David C. Drummond','女','12-12月-01','D2');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Alan Eustace','Jeff Huber','George Reyes','Elliot Schrage','Tim Armstrong','Nikesh Arora','Sukhinder','Vinton G. Cerf','Dave Girouard','Singh Cassidy','D2');
COMMIT;

--3. 在deptid成立索引
CREATE INDEX deptid_index on UserInfo(deptid);

--4. 给Department和UserInfo建设同义词，名称别离是dept,sy_user
CREATE OR REPLACE SYNONYM dept FOR Department;
CREATE OR REPLACE SYNONYM sy_user FOR UserInfo;

/*
5. 通过同义词dept,user来建设视图，
视图要求能查询出“部分名称，部分编号，用户名，性别，注册时刻”
*/
CREATE OR REPLACE VIEW view_dept_user
("部分名称","部分编号","用户名","性别","注册时刻")
AS SELECT d.deptName,d.deptID,u.userName,u.sex,u.loginDate 
  FROM dept d,sy_user u  
  WHERE d.deptID=u.deptid;

--14. 行使Instead Of触发器，往第5题建设的视图中插入数据。
SET SERVEROUTPUT ON;
INSERT INTO view_dept_user ('部分名称','部分编号','用户名','性别','注册时刻')
  VALUES('仓储部','D6','Dejan Perkovic','1-1月-08');

/*
CREATE OR REPLACE VIEW view_dept_user
("部分名称",sy_user u  
  WHERE d.deptID=u.deptid;
*/
CREATE OR REPLACE TRIGGER dept_user_insert
INSTEAD OF INSERT ON view_dept_user
FOR EACH ROW
DECLARE
   CURSOR cur_dept IS SELECT * FROM Department
   WHERE Department.deptID = :NEW.deptID;
   CURSOR cur_user IS SELECT * FROM sy_user
   WHERE sy_user.userName = :NEW.userName;
   d cur_dept%rowtype;
   u cur_user%rowtype;
   did dept.deptID%TYPE;
   uid sy_user.userID%TYPE;
BEGIN
   OPEN cur_dept;
   OPEN cur_user;
   FETCH cur_user INTO u;
   FETCH cur_dept INTO d;   
  /*
   假如插入的数据中部分ID不存在,则将 seq_Dep.NEXTVAL 发生的序号赋值给变量did,执行插入语句新增进一个部分。
   */
   IF cur_dept%NOTFOUND THEN
   did := 'D'||seq_Dep.NEXTVAL;
   INSERT INTO Department(deptID,deptName) VALUES(did,:NEW.deptName);
   /*
   假如插入数据中的员工不存在，则执行插入语句增进一个新员工
   */  
  IF cur_user%NOTFOUND THEN
 uid := 'U'||seq_User.NEXTVAL;
   INSERT INTO UserInfo(userID,deptid) VALUES(uid,:NEW.userName,:NEW.sex,:NEW.loginDate,did);
   END IF;
    
  IF cur_dept%FOUND THEN   
  /*
   假如插入的数据中部分ID是已经存在的部分ID,则更新部分名称
   */
   did:=:NEW.deptID;
   UPDATE Department SET Department.deptName WHERE Department.deptID = :NEW.deptID;
   /*
   假如插入数据中的员工不存在，则将seq_User.NEXTVAL 发生的序号赋值给变量uid,执行插入语句增进一个新员工
   */
   IF cur_user%NOTFOUND THEN
   uid := 'U'||seq_User.NEXTVAL;
   INSERT INTO UserInfo VALUES(uid,:NEW.deptID);
   ELSE
   /*
   假如插入数据中的员工已存在，则按照部分ID、员工姓名 来更新员工的其他字段的值，员工ID为主键，不消更新。
   思量到同名员工存在的也许，但员工的ID不行能沟通的特点，必要按照插入数据中的部分ID、员工姓名来查询出
   切合要求的员工的ID，用员工ID来举办后续的操纵
   */
   SELECT userID INTO uid FROM UserInfo WHERE UserInfo.userName=:NEW.userName AND UserInfo.deptID=did;
   
  UPDATE UserInfo SET UserInfo.sex = :NEW.sex,UserInfo.loginDate = :NEW.loginDate,UserInfo.deptID = :NEW.sex  
  WHERE UserInfo.userID=uid AND UserInfo.userName=:NEW.userName;
   END IF;
   END IF;
   CLOSE ecur;
   CLOSE dcur;
END dept_user_insert;
/
show errors;

呈现的错误提醒:

告诫: 建设的触发器带有编译错误。

SQL> show errors;
TRIGGER DEPT_USER_INSERT 呈现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/31 PLS-00049: 错误的赋值变量 'NEW.DEPTID'
5/30 PLS-00049: 错误的赋值变量 'NEW.USERNAME'
20/56 PLS-00049: 错误的赋值变量 'NEW.DEPTNAME'
26/75 PLS-00049: 错误的赋值变量 'NEW.USERNAME'
26/89 PLS-00049: 错误的赋值变量 'NEW.SEX'
26/98 PLS-00049: 错误的赋值变量 'NEW.LOGINDATE'
33/10 PLS-00049: 错误的赋值变量 'NEW.DEPTID'
34/4 PL/SQL: SQL Statement ignored
34/46 PL/SQL: ORA-00927: 缺傲尤号
34/72 PLS-00049: 错误的赋值变量 'NEW.DEPTID'
40/37 PLS-00049: 错误的赋值变量 'NEW.USERNAME'

LINE/COL ERROR
-------- -----------------------------------------------------------------
40/51 PLS-00049: 错误的赋值变量 'NEW.SEX'
40/60 PLS-00049: 错误的赋值变量 'NEW.LOGINDATE'
40/75 PLS-00049: 错误的赋值变量 'NEW.DEPTID'
47/66 PLS-00049: 错误的赋值变量 'NEW.USERNAME'
49/40 PLS-00049: 错误的赋值变量 'NEW.SEX'
49/70 PLS-00049: 错误的赋值变量 'NEW.LOGINDATE'
49/103 PLS-00049: 错误的赋值变量 'NEW.SEX'
50/53 PLS-00049: 错误的赋值变量 'NEW.USERNAME'
55/5 PLS-00103: 呈现标记 "DEPT_USER_INSERT"在必要下列之一时：
   if

以上内容由PHP站长网【52php.cn】网络清算供各人参考研究

假如以上内容对您有辅佐，接待保藏、点赞、保举、分享。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!