create view cruise_book_customer
as
select cruise_book.cruise_cd as cruise_book_code,
cruise_book.start_dt as cruise_book_start_date,
customer.start_dt as customer_start_date
from cruise_book, customer
where cruise_book.cruise_cd = customer.cruise_cd
gocreate trigger UpdStartDt
on cruise_book_customer
INSTEAD OF update
as
begin
if update(cruise_book_start_date)
print 'Start_date of cruise_book has modified!'
if update(customer_start_date)
print 'Start_date of customer has modified!'
end
go
as
select cruise_book.cruise_cd as cruise_book_code,
cruise_book.start_dt as cruise_book_start_date,
customer.start_dt as customer_start_date
from cruise_book, customer
where cruise_book.cruise_cd = customer.cruise_cd
gocreate trigger UpdStartDt
on cruise_book_customer
INSTEAD OF update
as
begin
if update(cruise_book_start_date)
print 'Start_date of cruise_book has modified!'
if update(customer_start_date)
print 'Start_date of customer has modified!'
end
go
该触发器代替触发操作执行。可在表和视图上指定 INSTEAD OF 触发器。只能为每个触发操作(INSERT、UPDATE 和 DELETE)定义一个 INSTEAD OF 触发器。INSTEAD OF 触发器可用于对 INSERT 和 UPDATE 语句中提供的数据值执行增强的完整性检查。INSTEAD OF 触发器还允许指定某些操作,使一般不支持更新的视图可以被更新。
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO 表1
SELECT name1,name2
FROM inserted
INSERT INTO 表2
SELECT name3,name4
FROM insertedEND
所以我认为应该改为:
CREATE TRIGGER 名 on 视图
INSTEAD OF INSERT
AS
BEGIN
DECLARE @InsertError1 int
DECLARE @InsertError2 int
BEGIN TRAN
INSERT INTO 表1
SELECT name1,name2
FROM inserted
SELECT @InsertError1 = @@Error
INSERT INTO 表2
SELECT name3,name4
FROM inserted
SELECT @InsertError2 = @@Error
IF @InsertError1 = 0 AND @InsertError2 = 0
COMMIT TRAN
ELSE
ROLLBACK TRANEND
但SQLSERVER2000可以它分为After,Instead of两种类型