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

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

  • 5
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值