你可以写到触发器里。
create trigger tri_table1_ins_update on table1 for update,insert
as
declare @shur_char varchar(100)
declare @shuc_char varchar(100)
select @shur_char=汉字字段 from inserted
exec dbo.PyPro @shuc_char output
update table1 set 拼音字段=@shuc_char where table1.主键=inserted.主键
go
create trigger tri_table1_ins_update on table1 for update,insert
as
declare @shur_char varchar(100)
declare @shuc_char varchar(100)
select @shur_char=汉字字段 from inserted
exec dbo.PyPro @shuc_char output
update table1 set 拼音字段=@shuc_char where table1.主键=inserted.主键
go
CREATE TRIGGER hztopy ON [dbo].[qpsxk]
FOR INSERT, UPDATE
AS
declare @shur_char varchar(100)
declare @shuc_char varchar(100)
select @shur_char=v_spmc from inserted
exec dbo.PyPro @shuc_char output
update qpsxk set v_py=@shuc_char where qpsxk.i_spid=inserted.i_spid
go现在报错,说inserted与查询表名和别名不匹配!
能说说吗?还有inserted在此是什么意思?为什么没有updated?
先谢谢了!
delete触发器对应的是deletedselect @shur_char=v_spmc from inserted
这句有问题,如果批量插入数据,@shur_char取值就有会问题,可能取得是最后一个insert语句中的值update qpsxk
set v_py=@shuc_char
from inserted a
where qpsxk.i_spid=a.i_spid
你有msn或者QQ吗?
我现在的触发器写好了,但没有达到我想要的效果!你们帮我看看我哪里不对?谢谢各位了!触发器:在qpsxk表中
CREATE TRIGGER hztopy ON [dbo].[qpsxk]
FOR INSERT, UPDATE
AS
declare @shur_char varchar(100)
declare @shuc_char varchar(100)
select @shur_char=v_spmc from qpsxk
exec dbo.PyPro @shuc_char output
update qpsxk set v_py=@shuc_char from inserted inser where qpsxk.i_spid=inser.i_spid存储过程
CREATE PROCEDURE dbo.PyPro @strchar char(200) output
AS
SET NOCOUNT ON;
declare @l_len int
declare @ln_i int
declare @lcpy char(100)
declare @lc_char char(100)
declare @py char(100)
set @strchar=upper(Rtrim(@strchar))
set @l_len=len(@strchar)
set @py=''
set @ln_i=1
while (@ln_i<=@l_len)
BEGIN
set @lcpy=substring(@strchar,@ln_i,1)
if (ascii(@lcpy)>=160)
begin
select @lc_char=py.py from py where hz=@lcpy
select @py=Rtrim(@py)+Rtrim(upper(@lc_char))
end
else
begin
select @py=Rtrim(@py)+Rtrim(upper(@lcpy))
end
set @ln_i=@ln_i+1
end
select @strchar= Rtrim(@py)
GO但当我填写qpsxk中v_spmc字段后,v_py字段没有出现相应的拼音!
我如果只是单独调试存储过程是可以有返回值的!
触发器代码如下,谢谢各位了,马上结账!
CREATE TRIGGER hztopy_qpsxk ON dbo.qpsxk
FOR INSERT, UPDATE
AS
if update(v_spmc)
begin
declare @shuc_char varchar(200)
select @shuc_char=v_spmc from inserted
exec dbo.PyPro @shuc_char output
update qpsxk set v_py=@shuc_char from inserted a where qpsxk.i_spid=a.i_spid
end