首先创建2个表!
USE[TEST]
IF OBJECT_ID('T_EMPLOYEE') IS NOT NULL
DROP TABLE T_EMPLOYEE
ELSE
CREATE TABLE T_EMPLOYEE(EMPLOYEE_ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
EMPLOYEE_NAME VARCHAR(20),
ADRESS VARCHAR(100),
PHONE VARCHAR(15),
DEPARTMENT_NAME VARCHAR(20)
)
GO
IF OBJECT_ID('T_DEPARTMENT') IS NOT NULL
DROP TABLE T_DEPARTMENT
ELSE
CREATE TABLE T_DEPARTMENT(DEPARTMENT_ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
DEPARTMENT_NAME VARCHAR(20),
EMPLOYEE_NAME VARCHAR(20),
EMPLOYEE_ID INT
)
GO
INSERT T_EMPLOYEE(EMPLOYEE_NAME,ADRESS,PHONE) VALUES('A1','A1','A1')
INSERT T_EMPLOYEE(EMPLOYEE_NAME,ADRESS,PHONE) VALUES('A2','A2','A2')
INSERT T_EMPLOYEE(EMPLOYEE_NAME,ADRESS,PHONE) VALUES('A3','A3','A3')
INSERT T_EMPLOYEE(EMPLOYEE_NAME,ADRESS,PHONE) VALUES('A4','A4','A4')
------------------------------------------创建触发器,-插入数据的时候触发-------------------------
If OBJECT_ID('trInsertEmployee') is not null
DROP TRIGGER trInsertEmployee
GO
CREATE TRIGGER [trInsertEmployee]
ON dbo.t_employee
AFTER INSERT
AS
--更新当前的编号
DECLARE @ID INT
--更新当前的名称
DECLARE @NAME VARCHAR(20)
--更新当前的的depart
DECLARE @DEPARTNAME VARCHAR(20)
--当前的模式 @modeint
--判断是否有记录被更改
IF @@ROWCOUNT>0
BEGIN
SELECT @ID=employee_id,@name=employee_name,@departname = department_name
FROM INSERTED
INSERT T_DEPARTMENT(EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT_NAME)VALUES
(@ID,@NAME,@DEPARTNAME)
END
--------------------------------------创建删除触发器------------------------------------------
If OBJECT_ID('trDeleteEmployee') is not null
DROP TRIGGER trDeleteEmployee
GO
CREATE TRIGGER [trDeleteEmployee]
ON dbo.t_employee
AFTER DELETE
AS
--更新当前的编号
DECLARE @ID INT
--判断是否有记录被更改
IF @@ROWCOUNT>0
BEGIN
SELECT @ID=EMPLOYEE_ID
FROM DELETED
DELETE FROM DBO.T_DEPARTMENT WHERE EMPLOYEE_ID = @ID
END
--------------------------------------创建修改触发器------------------------------------------
If OBJECT_ID('trUpdateEmployee') is not null
DROP TRIGGER trUpdateEmployee
GO
CREATE TRIGGER [trUpdateEmployee] ON DBO.T_EMPLOYEE
AFTER UPDATEAS
AS
--更新当前的编号
DECLARE @ID INT
--更新当前的名称
DECLARE @NAME VARCHAR(20)
--更新当前的的DEPART
DECLARE @DEPARTNAME VARCHAR(20)
--判断是否有记录被更改
IF @@ROWCOUNT>0
BEGIN
SELECT @ID=EMPLOYEE_ID,@NAME=EMPLOYEE_NAME,@DEPARTNAME=DEPARTMENT_NAME FROM Inserted
UPDATE DBO.T_DEPARTMENT SET EMPLOYEE_NAME=@NAME,DEPARTMENT_NAME=@DEPARTNAME WHERE EMPLOYEE_ID = @ID
END
-------注意:1.插入操作(Insert)
--Inserted表有数据,Deleted表无数据
--2.删除操作(Delete)
--Inserted表无数据,Deleted表有数据
--3.更新操作(Update)
--Inserted表有数据(新数据),Deleted表有数据(旧数据)