数据库表结构如下:
select * from TF_POS_Z
得到以下数据:
OS_ID OS_NO ITM SCDH SDDH SSJZ
PO GFPO1010430 1 NULL Y10-1563 YM050#680(D2467)
PO GFPO1010430 2 NULL Y10-1480 YM050#808(D3101)
PO GFPO1010430 3 NULL Y10-1613 YM050#870(D3036)
现在要在TF_POS_Z表上面建立一个触发器,实现空值列SCDH=SDDH+' '+SSJZ , 以下为我自己写的触发器,但是有问题ALTER TRIGGER UP_SCDH ON TF_POS_Z AFTER INSERT
AS
declare @scdh varchar(50)
declare @os_no varchar(30)
declare @os_id varchar(5)
declare @ssjz varchar(50)
declare @sddh varchar(50)
select @os_id=b.os_id, @os_no=b.os_no,@scdh=a.scdh, @ssjz=b.ssjz, @sddh=b.sddh from tf_pos_z a, inserted b where a.os_no=b.os_no and a.os_id='PO'
if (ISNULL(@ssjz,'')<>'' and ISNULL(@scdh,'')='' and isnull(@os_id,'')='PO')
begin
UPDATE A SET A.SCDH=B.SDDH+' '+B.SSJZ
FROM TF_POS_Z A, INSERTED B
WHERE A.OS_NO=B.OS_NO AND A. OS_ID='PO' AND A.SCDH IS NULL OR A.SCDH=''
end请教各位高手帮忙,修改
select * from TF_POS_Z
得到以下数据:
OS_ID OS_NO ITM SCDH SDDH SSJZ
PO GFPO1010430 1 NULL Y10-1563 YM050#680(D2467)
PO GFPO1010430 2 NULL Y10-1480 YM050#808(D3101)
PO GFPO1010430 3 NULL Y10-1613 YM050#870(D3036)
现在要在TF_POS_Z表上面建立一个触发器,实现空值列SCDH=SDDH+' '+SSJZ , 以下为我自己写的触发器,但是有问题ALTER TRIGGER UP_SCDH ON TF_POS_Z AFTER INSERT
AS
declare @scdh varchar(50)
declare @os_no varchar(30)
declare @os_id varchar(5)
declare @ssjz varchar(50)
declare @sddh varchar(50)
select @os_id=b.os_id, @os_no=b.os_no,@scdh=a.scdh, @ssjz=b.ssjz, @sddh=b.sddh from tf_pos_z a, inserted b where a.os_no=b.os_no and a.os_id='PO'
if (ISNULL(@ssjz,'')<>'' and ISNULL(@scdh,'')='' and isnull(@os_id,'')='PO')
begin
UPDATE A SET A.SCDH=B.SDDH+' '+B.SSJZ
FROM TF_POS_Z A, INSERTED B
WHERE A.OS_NO=B.OS_NO AND A. OS_ID='PO' AND A.SCDH IS NULL OR A.SCDH=''
end请教各位高手帮忙,修改
解决方案 »
- 请教一个sql语句问题
- 请教一个SQL语法,我想修改一个字段的类型
- 如何保存数据库的资料?
- 如何在sqlserver2005里面添加远程数据库?
- 关于sql统计数量的问题count()
- 求助:Sql语句问题。
- 关于数据库存储过程执行超时的问题?请各位给点意见。。。
- 领导让我这么作我总觉得不合理,请各们帮忙看看
- 新建一个帐号连接Oracle,出现“已拒绝对 OLE DB 提供程序 'MSDAORA' 的特殊访问。必须通过链接服务器来访问此提供程序。”,用sa可以
- 如何处理java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
- SQL2005,能直接打SP3的补丁吗?
- 大家来说这个结果怎样写最好?
AS
begin
UPDATE A SET A.SCDH=B.SDDH+' '+B.SSJZ
FROM TF_POS_Z A, INSERTED B
WHERE A.OS_NO=B.OS_NO AND A. OS_ID='PO' AND A.SCDH IS NULL OR A.SCDH=''
end
AS
begin
UPDATE A SET A.SCDH=B.SDDH+' '+B.SSJZ
FROM TF_POS_Z A, INSERTED B
WHERE A.OS_NO=B.OS_NO AND a.os_id='PO' and b.OS_ID is null AND A.SCDH IS NULL and b.ssjz is null --在这里加上你的条件即可。
end
AS
begin
UPDATE A SET A.SCDH=B.SDDH+' '+B.SSJZ
FROM TF_POS_Z A, INSERTED B
WHERE A.OS_NO=B.OS_NO AND A. OS_ID='PO' AND A.SCDH IS NULL OR A.SCDH=''
end--呵呵,插入表TF_POS_Z后,又更改TF_POS_Z的值,怕死锁吗?
同时,如果手工插入TF_POS_Z表中数据时也可以增加, TF_POS_Z表是自定义采购单表。请教各位!!