?? archdb.ddl
字號:
CREATE TABLE AccountTable (
UserName CHAR ( 12 ) NOT NULL,
Password CHAR ( 20 ) NOT NULL,
AccountRight CHAR(7) NOT NULL,
CONSTRAINT PK_AccountTable0 PRIMARY KEY NONCLUSTERED (UserName),
CONSTRAINT TC_AccountTable2 UNIQUE NONCLUSTERED (UserName)
)
GO
CREATE TABLE StuffTable (
Name CHAR ( 12 ) NOT NULL,
ArchiveNo INT NOT NULL,
Birthday DATETIME NOT NULL,
Degree CHAR ( 10 ) NOT NULL,
Posistion CHAR ( 40 ) NOT NULL,
JobStartDate DATETIME NOT NULL,
JobEndDate DATETIME NOT NULL,
Note TEXT,
CreateTime DATETIME NOT NULL,
DepNo INT NOT NULL,
Creater CHAR ( 10 ) NOT NULL,
Gender char ( 2 ) NOT NULL,
Address char ( 30 ),
TelNumber Char ( 10 ),
CONSTRAINT PK_StuffTable1 PRIMARY KEY NONCLUSTERED (ArchiveNo),
CONSTRAINT TC_StuffTable1 UNIQUE NONCLUSTERED (ArchiveNo)
)
GO
CREATE TABLE DepartmentTable (
DepNo INT IDENTITY NOT NULL,
DepName CHAR ( 10 ) NOT NULL,
DepLeader CHAR ( 10 ),
DepMemberNo INT NOT NULL,
CONSTRAINT PK_DepartmentTable3 PRIMARY KEY NONCLUSTERED (DepNo),
CONSTRAINT TC_DepartmentTable3 UNIQUE NONCLUSTERED (DepNo)
)
GO
/* Template: Parent SET NULL DELETE in SQL SERVER */
CREATE TRIGGER TRIG_StuffTable0 ON DepartmentTable FOR DELETE
AS
UPDATE StuffTable SET DepNo = NULL FROM StuffTable, deleted
WHERE StuffTable.DepNo = deleted.DepNo
GO
/* Template: Parent CASCADE UPDATE in SQL SERVER */
CREATE TRIGGER TRIG_StuffTable1 ON DepartmentTable FOR UPDATE
AS
IF UPDATE(DepNo)
BEGIN
DECLARE @DepNo_New INT
DECLARE @DepNo_Old INT
DECLARE DepartmentTable_New CURSOR FOR SELECT DepNo FROM inserted
DECLARE DepartmentTable_Old CURSOR FOR SELECT DepNo FROM deleted
OPEN DepartmentTable_New
OPEN DepartmentTable_Old
FETCH NEXT FROM DepartmentTable_New
INTO @DepNo_New
FETCH NEXT FROM DepartmentTable_Old
INTO @DepNo_Old
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE StuffTable SET DepNo = @DepNo_New
WHERE DepNo = @DepNo_Old
FETCH NEXT FROM DepartmentTable_New
INTO @DepNo_New
FETCH NEXT FROM DepartmentTable_Old
INTO @DepNo_Old
END
CLOSE DepartmentTable_New
DEALLOCATE DepartmentTable_New
CLOSE DepartmentTable_Old
DEALLOCATE DepartmentTable_Old
END
GO
/* Template: Child RESTRICT Insert in SQL server*/
CREATE TRIGGER TRIG_StuffTable2 ON StuffTable FOR INSERT
AS
BEGIN
DECLARE @DepNo INT
DECLARE StuffTable_New CURSOR FOR SELECT DepNo FROM inserted
OPEN StuffTable_New
FETCH NEXT FROM StuffTable_New
INTO @DepNo
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DepNo IS NOT NULL
BEGIN
IF NOT EXISTS (SELECT * FROM DepartmentTable WHERE DepNo = @DepNo)
BEGIN
RAISERROR('CANNOT INSERT StuffTable BECAUSE DepartmentTable DOESN''T EXIST.', 16, 1)
ROLLBACK TRANSACTION
BREAK
END
END
FETCH NEXT FROM StuffTable_New
INTO @DepNo
END
CLOSE StuffTable_New
DEALLOCATE StuffTable_New
END
GO
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -