推荐订阅源

--instead of 触发器演示脚本。

create view ca_view   --创建ca_view视图
as
select au_id,au_lname +'.'+ au_fname as au_name,phone  --注意：au_name 列是计算列，中间有个'.'
from authors
where state= 'ca'

create trigger tri_caview
on ca_view                       --在ca_view视图上建立
instead of update                --instead of 更新触发器
as
declare @name varchar(40),       --声明一系列变量
        @lname varchar(20),
        @fname varchar(20),
        @au_id varchar(20),
        @dot char,
        @n int       
if update(au_name)
  begin
    select @au_id = au_id, @name = au_name from inserted  --赋值语句
    set @dot = '.'                            
    set @n = charindex(@dot,@name)                        --将根据'.'区分lname和fname
    set @lname = left(@name,@n-1)                         --得到lname
    set @fname = right(@name,len(@name) - @n )            --得到fname
    update authors set au_lname = @lname,au_fname = @fname    --更新authors表
      Where au_id = @au_id
  end
go

update ca_view set au_name='HJ.Zhang' where au_id = '123-45-6789'

insert into authors(au_id,au_lname,au_fname,phone,contract,state)
values('123-45-6789','haijian','zhang','410 444-1111',1,'CA')