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

1.编写一个更新触发器，实现安全性控制：只有数据库拥有者(dbo)才可以修改员工表中的薪水，且一次只能修改一条记录，并将修改前后的值添加到审计表中。

/*记录用户的操作轨迹，首先创建一张审计表*/
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

2.编写一个插入触发器，实现完整性约束：当销售明细表中插入某产品的销售数据时，如果销售数量低于实际库存量，则取消产品的当次销售；否则，及时更新产品库存数量，若销售的产品数量在本次销售后库存量低于该产品最低库存量，则提示增加库存信息。

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

3.创建触发器，该触发器仅允许“dbo”用户可以删除Customer表内数据。

create trigger CustomerDelete
on Customer for delete
as
	if exists(select * from deleted)
	begin
		if USER != 'dbo'
		begin
			print '该用户无权限删除Customer表内数据！'
			rollback
		end
	end

4.创建触发器，要求当修改Employee表中员工的出生日期或雇佣日期时，必须保证出生日期在雇佣日期之前，且雇佣日期与出生日期之间必须间隔16周年及以上。

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