关于view的触发器的例子

新客网 XKER.COM 2004-08-19 来源： 收藏本文

/*create table UserTable
(
UserId uniqueidentifier NOT NULL ROWGUIDCOL, -- 唯一标识
Name nvarchar(60) NULL, -- 名

age int NULL DEFAULT 0 check(age>20) ,-- 年龄

address nvarchar(25) NULL --地址
)*/
go

create table LogTable1
(
name nvarchar(60) null,
age int
)
go
create table LogTable2
(
name nvarchar(60) null,
age int
)

go
insert LogTable1 values ( 't11',30)

insert LogTable1 values ( 't12',40)

insert LogTable1 values ( 't13',50)

insert LogTable2 values ( 't21',130)

insert LogTable2 values ( 't22',140)

insert LogTable2 values ( 't23',150)
insert LogTable2 values ( 't24',160)

select * into logAll from logtable1 union select * from logtable2


go
--create view
Create view logView
as
select * from logtable1 union select * from logtable2

--create trigger of delete in view
create trigger logView_delete on logView instead of delete
as
begin
delete logAll where name in (select name from deleted)
end
go

--create trigger of insert in view
create trigger logView_insert on logView instead of insert
as
begin
insert logAll select * from inserted
end
go


--create trigger of update in view
create trigger logView_update on logView instead of update
as
begin
set nocount off
if not exists(select * from inserted) return

declare @name varchar(20)
declare @oldname varchar(20)
declare @age int

select @name = name,@age=age from inserted
select @oldname=name from deleted

update logtable1 set name=@name,age=@age where name=@oldname
update logtable2 set name=@name,age=@age where name=@oldname
/*

if @namer is null and @age is null
begin
raiserror ( 1074008065, 16, 127 ) with nowait, seterror
return
end

if(UPDATE(name))-
begin
EXEC p_GetName
end
*/


end


--test
delete logview where name='t11'

insert logview values('v1',100)


update logView set name='t1212',age=10000 where name='t12'

• ·ASP+ACCESS数据库中文乱码问题解决
• ·编程实例 字母+数字验证码程序
• ·ASP连接数据库错误解决办法新法
• ·初学:ASP内建对象Response
• ·ASP网站Server object error的解决办法
• ·ASP技巧：让Len,Left,Right函数识别中文
• ·把网页中的电话号码生成图片的ASP程序
• ·ASP如何实现IE地址栏参数的判断
• ·细化解析：ASP连接11种数据库语法集锦
• ·ASP实现网页打开任何类型文件都保存
• ·ASP代码直接增加、删除ACCESS表和字段
• ·ASP：判断访问是否来自搜索引擎的函数
• ·ASP技巧：禁用页面缓存的五种方法
• ·学习ASP关于与变量子类型相关的函数
• ·ASP开发中的(VBScript)类基础学习
• ·ASP访问Excel文件