Sql 触发器练习

首先创建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表有数据（旧数据）