SQLServer由于主外鍵約束導致插入失敗的記錄方法

--創建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','男','12-9月-07','D1');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Jeff Huber','男','12-10月-07','D3');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'George Reyes','男','12-11月-07','D5');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Elliot Schrage','男','1-12月-07','D4');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Tim Armstrong','女','12-12月-01','D2');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Nikesh Arora','女','12-12月-01','D2');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Sukhinder','男','12-9月-07','D1');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Vinton G. Cerf','男','12-10月-07','D3');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'George Reyes','男','12-11月-07','D5');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Dave Girouard','男','1-12月-07','D4');
INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Singh Cassidy','女','12-12月-01','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
("部門名稱","部門編號","用戶名","性別","注冊時間")
AS SELECT d.deptName,d.deptID,u.userName,u.sex,u.loginDate 
 FROM dept d ,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,userName,sex,loginDate,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.userName,:NEW.sex,:NEW.loginDate,: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