sql 触发器的题目

什么数据库的触发器啊？

--补充

create table users(

id int,

status char(1)

)

触发器

create or replace trigger trig_users

before insert on users

for each row

begin

if :new.status = '1' then

:new.status := 'a'

elsif :new.status = '2' then

:new.status := 'b'

end if

end

测试

SQL>insert into users

2 select 1,'1' from dual union

3 select 2,'2' from dual union

4 select 3,'1' from dual union

5 select 4,'2' from dual

6

4 rows inserted

SQL>select * from users

2

ID STATUS

--------------------------------------- ------

1 a

2 b

3 a

4 b

SQL>

一.建表

create table logdata(lid int , table_name char(30),update_type char(20),update_user char(20),update_date date)

二.建一个自增长序列

create sequence SEQ_D

minvalue 1

maxvalue 99999999

start with 1

increment by 1

cache 20

三.建触发器

列：studcnts

CREATE OR REPLACE TRIGGER TR_SEC_SCHOOL

BEFORE INSERT OR UPDATE OR DELETE ON STUDCNTS

BEGIN

CASE

WHEN INSERTING THEN

INSERT INTO LOGDATA VALUES(SEQ_D.NEXTVAL ,'STUDCNTS','INSERT',USER,SYSDATE)

WHEN UPDATING THEN

INSERT INTO LOGDATA VALUES(SEQ_D.NEXTVAL ,'STUDCNTS','UPDATE',USER,SYSDATE)

WHEN DELETING THEN

INSERT INTO LOGDATA VALUES(SEQ_D.NEXTVAL ,'STUDCNTS','DELETE',USER,SYSDATE)

END CASE

END

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 行受影响)

---