老师让做一个插入数据时 验证插入的数据中某字段的值 并把数据插入另一张表中 的触发器
老师说要用到游标 哪位大神给解释一下游标怎么用啊 这个触发器如何写啊
------解决思路----------------------
我刚解答了一个帖子，你可以看看游标是如何用的
http://bbs.csdn.net/topics/390887609?page=1#post-398198791
触发器的请看我的博文
http://blog.csdn.net/dotnetstudio/article/details/10396439
------解决思路----------------------
举个例子吧,下面这个例子是插入A表的数据,如果customer_id='0001',我就把该条数据另外插入到另外B表中.你可以根据自己的需求修改条件即下面脚本触发器游标中间处理部分。
USE TEST
GO
CREATE TABLE dbo.A
(
tran_no char(5) null,
part_id char(4) null,
quantity int null,
unit_price decimal(10,2) null,
customer_id char(4) null
)
CREATE TABLE dbo.B
(
tran_no char(5) null,
part_id char(4) null,
quantity int null,
unit_price decimal(10,2) null,
customer_id char(4) null,
editdate datetime null
)
GO
CREATE TRIGGER INSERT_A
ON DBO.A
AFTER INSERT
AS
DECLARE @inserted_tran_no CHAR(5),@inserted_part_id CHAR(4)
DECLARE @inserted_quantity INT
DECLARE @inserted_unit_price DECIMAL(10,2)
DECLARE @inserted_customerid CHAR(4)
DECLARE my_cursor CURSOR
LOCAL
STATIC
FORWARD_ONLY
READ_ONLY
FOR SELECT inserted.tran_no,inserted.part_id,inserted.quantity,inserted.unit_price,inserted.customer_id
FROM inserted
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @inserted_tran_no,@inserted_part_id,@inserted_quantity,@inserted_unit_price,@inserted_customerid
WHILE @@fetch_status = 0
BEGIN
IF @inserted_customerid = '0001'
BEGIN
INSERT INTO dbo.B(tran_no,part_id,quantity,unit_price,customer_id,editdate)
SELECT @inserted_tran_no, @inserted_part_id,@inserted_quantity,@inserted_unit_price,@inserted_customerid,getdate()
END
FETCH NEXT FROM my_cursor INTO @inserted_tran_no,@inserted_part_id,@inserted_quantity,@inserted_unit_price,@inserted_customerid
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
INSERT INTO dbo.A
SELECT '00001','0001',20,12.3,'0000'
UNION ALL
SELECT '00001','0001',20,12.3,'0001'
UNION ALL
SELECT '00001','0001',20,12.3,'0002'
UNION ALL
SELECT '00002','0001',20,12.3,'0001'
UNION ALL
SELECT '00005','0001',20,12.3,'0001'
UNION ALL
SELECT '00005','0001',20,12.3,'0003'
GO
SELECT * FROM DBO.A
SELECT * FROM DBO.B