【数据库学习日记】——编写触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/*记录用户的操作轨迹，首先创建一张审计表*/
create table EmployeeTrace(
	userid			char(10)		NOT NULL,	/* 用户标识 */
	number			int				NOT NULL,	/* 操作次数 */
	operateDate		datetime		NOT NULL,	/* 操作时间 */
	operateType		char(10)		NOT NULL,	/* 操作类型：插入/删除/修改 */
	employeeNo		char(8)			NOT NULL,   /*员工编号*/
	employeeName	varchar(10)		NOT NULL,	/*员工姓名*/
	newSalary		numeric(8,2)	NOT NULL,   /*修改后的薪资*/
	oldSalary		numeric(8,2)	NOT NULL,	/*修改前的薪资*/	
	constraint EmployeeTracePK primary key(userid,number))
	go
/*创建触发器*/
create trigger SalaryTracUpd				
on Employee for update as
begin
	declare @employeeNo char(8),@employeeName varchar(10),@newSalary numeric(8,2),@oldSalary numeric(8,2)
	declare @num int
	if user<> 'dbo' and exists(select *from deleted)
		rollback
	else if(select count(*) from deleted)>1
			rollback
		else
		begin
			select @num = MAX(number)
			from EmployeeTrace
			where userid = 'dbo'
			if @num is null
				set @num = 0
		declare curTrance cursor for
		select @employeeNo,@employeeName,@oldSalary from deleted
		select @newSalary from inserted
		open curTrance
		fetch curTrance into @employeeNo,@employeeName,@newSalary,@oldSalary
		while(@@FETCH_STATUS = 0)
		begin
			set @num = @num+1
			insert into EmployeeTrace values('dbo',@num,getdate(),'insert',@employeeNo,@employeeName,@newSalary,@oldSalary)
			fetch curTrance into @employeeNo,@employeeName,@newSalary,@oldSalary
		end
		close curTrance
		deallocate curTrance
		end
end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create trigger OrderDetailUpd
on OrderDetail for insert
as
begin
	declare @ProductQuantity int,@productStock  numeric(7,2),@productMinstock  numeric(7,2)
	select @ProductQuantity = quantity from OrderDetail
	select @productMinstock = productMinstock,@productStock = productStock from Product
	if exists (select @ProductQuantity from inserted where @ProductQuantity > @productStock)
		rollback
	else
	begin
		update Product
		set @productStock = @productStock - @ProductQuantity
		if (@productStock < @productMinstock)
			print('当前库存量低于该产品最低库存量，请增加库存！')
	end
end

1
2
3
4
5
6
7
8
9
10
11
create trigger CustomerDelete
on Customer for delete
as
	if exists(select * from deleted)
	begin
		if USER != 'dbo'
		begin
			print '该用户无权限删除Customer表内数据！'
			rollback
		end
	end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create trigger EmployeeUpdate 
on Employee for update
as
	declare @birthday datetime,@hiredate datetime
	if(UPDATE(birthday)or UPDATE(hireDate))
	begin
		declare getEmpCur cursor for
		select birthday,hireDate
		from inserted
		open getEmpCur
		fetch getEmpCur into @birthday,@hiredate
		while(@@FETCH_STATUS = 0)
		begin
			if(@hiredate<=@birthday)
			rollback
			else
				if YEAR(@hiredate)-YEAR(@birthday)<16
				rollback
			fetch getEmpCur into @birthday,@hiredate
		end
		close getEmpCur
		deallocate getEmpCur
	end