Question

数据库触发器问题

1、在score表上创建一个instead of update触发器，实现：当从表score更新记录时检查当前用户是否是dbo，如果不是dbo就不允许进行更新操作，否则允许。
2、给sell表创建一个after insert 触发器，实现：当向sell插入一条记录时，修改stock表stock_amount(库存数量)，值等于stock_amount-sell_amount，还有修改时间modified_date，并显示相应的提示信息：“库存量还剩余stock_amount-sell_amount”，当(stock_amount-sell_amount)<0时，不允许进行插入操作，即操作回滚，并显示相应的提示信息：“库存量不足，只有stock_amount”。

Answer 1

CREATE TABLE score (
id INT PRIMARY KEY,
val VARCHAR(10)
);

CREATE TRIGGER tr_Update_score
ON score
instead of update
AS
BEGIN
IF USER_NAME() = 'dbo'
UPDATE
score
SET
score.val = inserted.val
FROM
score JOIN inserted
ON (score.id = inserted.id)
ELSE
PRINT '你不是 DBO!';
END;

insert into score VALUES(1, 'A');

-- 使用 Demo 作为用户名，登录到数据库。
E:\>sqlcmd -S "localhost\SQLEXPRESS" -U Demo -P demo
1> use testwork
2> go
已将数据库上下文更改为 'TestWork'。
1> UPDATE score SET val='B' WHERE id = 1;
2> go

(1 行受影响)
你不是 DBO!
1> select * FROM score;
2> go
id val
----------- ----------
1 A

(1 行受影响)

-- 使用操作系统验证，登录到数据库。
E:\>sqlcmd -S "localhost\SQLEXPRESS"
1> use testwork
2> go
已将数据库上下文更改为 'TestWork'。
1> UPDATE score SET val='B' WHERE id = 1;
2> go

(1 行受影响)
1> select * FROM score;
2> go
id val
----------- ----------
1 B

(1 行受影响)

======================

--货物表
CREATE TABLE stock(
id INT,
stock_amount INT
);

--订单表
CREATE TABLE sell(
ID INT,
GoodsID INT,
sell_amount INT
);

-- 库存测试数据：
INSERT INTO stock VALUES (1, 100);

create trigger trgAfterSell
on sell
after insert
as
begin
declare
@cGoodsID as int,
@sell_amount as int,
@nowCount as INT

select @cGoodsID = GoodsID, @sell_amount = sell_amount
from inserted

SELECT @nowCount = stock_amount
FROM stock
where ID = @cGoodsID;

IF @nowCount - @sell_amount < 0
BEGIN
PRINT '库存量不足，只有 ' + CAST(@nowCount AS varchar);
ROLLBACK;
END
ELSE
BEGIN
update stock
set stock_amount = stock_amount - @sell_amount
where ID = @cGoodsID
PRINT '库存量还剩余' + CAST ((@nowCount - @sell_amount) AS varchar);
END
end

1> INSERT INTO sell VALUES(1, 1, 90);
2> go

(1 行受影响)
库存量还剩余10
1> INSERT INTO sell VALUES(1, 1, 20);
2> go
库存量不足，只有 10
消息 3609，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
事务在触发器中结束。批处理已中止。
1> select * from sell;
2> select * from stock;
3> go
ID GoodsID sell_amount
----------- ----------- -----------
1 1 90

(1 行受影响)
id stock_amount
----------- ------------
1 10

(1 行受影响)

追问

-- 使用 Demo 作为用户名，登录到数据库。
E:\>sqlcmd -S "localhost\SQLEXPRESS" -U Demo -P demo
1> use testwork
2> go
已将数据库上下文更改为 'TestWork'。
1> UPDATE score SET val='B' WHERE id = 1;
2> go

(1 行受影响)
你不是 DBO!
1> select * FROM score;
2> go
id val
怎么用其他用户登陆？我创建一个用户都不能登陆的，只有默认的可以啊

追答

-- 首先在 SQL Server 服务器级别，创建登陆帐户（create login）
1> CREATE LOGIN test_user
2> WITH PASSWORD='test',
3> DEFAULT_DATABASE=[Stock],
4> DEFAULT_LANGUAGE=[简体中文],
5> CHECK_EXPIRATION=OFF,
6> CHECK_POLICY=OFF
7> GO

-- 创建数据库用户（create user）
2> USE [Stock]
3> GO
已将数据库上下文更改为 'Stock'。
1> CREATE USER [test_user] FOR LOGIN [test_user] WITH DEFAULT_SCHEMA=[dbo]
2> GO

1> -- 通过将 test_user 加入数据库角色 db_owner
2> exec sp_addrolemember 'db_owner', 'test_user'
3> go

然后 test_user 就可以登录了。

Answer 2

create trigger score_update
on score
instead of update
as
begin
declare @sno1 char(9),@sno2 char(9),@current_user char(9)
select @sno1=sno from inserted
select @sno2=sno from deleted
if(current_user !='dbo')
print '不是dbo用户不允许进行更新操作'
else
update score set sno=@sno1 where sno=@sno2
end