Question

sql server 2005触发器

create table score
(
sno char(9),
cno int,
grade int,
)
CREATE TABLE stock(
prod_id char(10),
prod_name varchar(10),
unit_price int,
stock_amount int,
modified_date datetime
)
CREATE TABLE sell(
order_id int identity(1,1),
prod_id char(10),
sell_amount int,
sell_date datetime,
Saler varchar(10)
)
在score表上创建一个instead of update触发器，实现：当从表score更新记录时检查当前用户是否是dbo，如果不是dbo就不允许进行更新操作，否则允许。
给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 行受影响)

追问

修改时间modified_date，这个呢你缩水也缩的过分了吧

追答

CREATE TABLE stock(
prod_id char(10),
prod_name varchar(10),
unit_price int,
stock_amount int,
modified_date datetime
);
CREATE TABLE sell(
order_id int identity(1,1),
prod_id char(10),
sell_amount int,
sell_date datetime,
Saler varchar(10)
) ;
INSERT INTO stock
VALUES('Apple00001', '苹果', 1, 100, NULL);

create trigger trgAfterSell
on sell
after insert
as
begin
declare
@cGoodsID as char(10),
@sell_amount as int,
@sell_date as datetime,
@nowCount as INT

select
@cGoodsID = prod_id,
@sell_amount = sell_amount,
@sell_date = sell_date
from inserted;

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

IF @nowCount - @sell_amount INSERT INTO sell VALUES
2> ('Apple00001', 90, '2011-05-23 10:00:00', '张三');
3> go
(1 行受影响)
库存量还剩余10

1> INSERT INTO sell VALUES
2> ('Apple00001', 20, '2011-05-24 09:00:00', '李四');
3> go
库存量不足，只有 10
消息 3609，级别 16，状态 1，服务器 ZQWANG-RD\SQLEXPRESS，第 1 行
事务在触发器中结束。批处理已中止。

1> select * from sell;
2> select * from stock;
3> go
order_id prod_id sell_amount sell_date Saler
----------- ---------- ----------- ----------------------- ----------
1 Apple00001 90 2011-05-23 10:00:00.000 张三
(1 行受影响)

prod_id prod_name unit_price stock_amount modified_date
---------- ---------- ----------- ------------ -----------------------
Apple00001 苹果 1 10 2011-05-23 10:00:00.000
(1 行受影响)

Answer 2

CREATE TRIGGER tri_Insert
ON OrderList
FOR INSERT
AS
DECLARE
@CustomerNo 数据类型不明;
BEGIN
-- 取得插入的 CustomerNo
SELECT @CustomerNo = CustomerNo FROM INSERTED;

IF NOT EXISTS ( SELECT * FROM Customer WHERE CustomerNo = @CustomerNo )
BEGIN
-- 不允许记录被插入，删除记录
DELETE FROM OrderList WHERE OrderNo = (SELECT OrderNo FROM INSERTED);

-- 抛出异常
RAISERROR (错误代码， 错误严重级别， 错误状态代码);
END

END