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