要更新的表名QT_Customer,插入记录时按照QT_Customer的QT_BindPhone(号码字段),,判断QT_BindPhone开头是0还是非0,是0就前三位判断是不是020,之后再找前四位(按照另一个表BSS_CODE(地区代码表)的RegionCode对照区号,如果是的话,将BSS_CODE.Region的写入QT_Customer的QT_Area字段和QT_AreaCode中,如果是手机的话,找BSS_Mobile表内的BSS_Mobile.MobileHead前七位,如果相同就将BSS_Mobile.CityName和BSS_Mobile.CityCode写入QT_Customer表去,分别对应:QT_Area和QT_AreaCode
------------------------------------
------我写的触发器,请问问题和改正:
----------------------- CREATE TRIGGER in_data on QT_Customer for insert as
if(LEFT(QT_Customer.QT_BindPhone,1)= '0 ')
BEGIN if(LEFT(QT_Customer.QT_BindPhone,3) = '020 ')
BEGIN update QT_Customer c ,inserted i set QT_Customer.QT_AreaCode= '020 ',QT_Customer.QT_Area= '广州 ' where c.QT_CustID=i.QT_CustID ;
END
ELSE
BEGIN update QT_Customer as q left join BSS_CODE as b on q. QT_AreaCode =b.RegionCode set q.QT_AreaCode=b.RegionCode ,q.QT_Area=b.Region where
b.RegionCode =LEFT(q.QT_BindPhone,3); END
END
ELSE BEGIN
update QT_Customer as q left join BSS_Mobile as b on q. QT_AreaCode =b.CityCode set q.QT_AreaCode=b.CityCode ,q.QT_Area=b.CityName where
b.MobileHead =LEFT(q.QT_BindPhone,3) and c.QT_CustID=i.QT_CustID ; END
------------------------------------
------我写的触发器,请问问题和改正:
----------------------- CREATE TRIGGER in_data on QT_Customer for insert as
if(LEFT(QT_Customer.QT_BindPhone,1)= '0 ')
BEGIN if(LEFT(QT_Customer.QT_BindPhone,3) = '020 ')
BEGIN update QT_Customer c ,inserted i set QT_Customer.QT_AreaCode= '020 ',QT_Customer.QT_Area= '广州 ' where c.QT_CustID=i.QT_CustID ;
END
ELSE
BEGIN update QT_Customer as q left join BSS_CODE as b on q. QT_AreaCode =b.RegionCode set q.QT_AreaCode=b.RegionCode ,q.QT_Area=b.Region where
b.RegionCode =LEFT(q.QT_BindPhone,3); END
END
ELSE BEGIN
update QT_Customer as q left join BSS_Mobile as b on q. QT_AreaCode =b.CityCode set q.QT_AreaCode=b.CityCode ,q.QT_Area=b.CityName where
b.MobileHead =LEFT(q.QT_BindPhone,3) and c.QT_CustID=i.QT_CustID ; END
BEGIN if(LEFT(QT_Customer.QT_BindPhone,3) = '020 ')
---这样的判断都是语法错误
CREATE TRIGGER in_data on QT_Customer for insert
as
DECLARE @phone VARCHAR
select @phone=QT_Customer.QT_BindPhone from QT_Customerif(LEFT(@phone,3) ='020')
BEGIN
update QT_Customer set QT_AreaCode='020',QT_Area='广州' ;
END ....
我想知道临时表怎么用..
select @phone=QT_Customer.QT_BindPhone from QT_Customer
这样的写法都是不支持批量插入数据。。只能一条一条insert
列出一些数据来说明,大家比较容易明白如果是一条一条insert时,用变量判断当然比较好,用支持批量insert时就不能这样写了
on QT_Customer
for insert
as --020
if exists (select 1 from INSERTED where QT_BindPhone like '020%')
begin
update QT_Customer
set QT_Customer.QT_AreaCode='020',QT_Customer.QT_Area='广州'
from QT_Customer
inner join INSERTED on QT_Customer.QT_CustID=INSERTED.QT_CustID
where INSERTED.QT_BindPhone like '020%'
end--0开头非020
if exists (select 1 from INSERTED where QT_BindPhone like '[0][^2][^0]%')
begin
update QT_Customer
set QT_Customer.QT_AreaCode=BSS_CODE.RegionCode,QT_Customer.QT_Area=BSS_CODE.Region
from QT_Customer
inner join INSERTED on QT_Customer.QT_CustID=INSERTED.QT_CustID
left join BSS_CODE on QT_Customer.QT_AreaCode=BSS_CODE.RegionCode
where INSERTED.QT_BindPhone like '[0][^2][^0]%'
and BSS_CODE.RegionCode =left(QT_Customer.QT_BindPhone,3)
end--非0开头
if exists (select 1 from INSERTED where QT_BindPhone like '[^0]%')
begin
update QT_Customer
set QT_Customer.QT_AreaCode=b.CityCode,QT_Customer.QT_Area=b.CityName
from QT_Customer
inner join INSERTED on QT_Customer.QT_CustID=INSERTED.QT_CustID
left join BSS_Mobile on QT_Customer.QT_AreaCode=BSS_Mobile.CityCode
where INSERTED.QT_BindPhone like '[^0]%'
and BSS_Mobile.MobileHead=left(QT_Customer.QT_BindPhone,3)
-- and c.QT_CustID=i.QT_CustID
--inner join INSERTED 的联接条件
endgo
认真看了表结构和更新逻辑,修正如下:
*/CREATE TRIGGER in_data
on QT_Customer
for insert
as set nocount on--0固话:3位和4位区号一起处理
if exists (select 1 from INSERTED where QT_BindPhone like '[0]%')
begin
update QT_Customer
set QT_Customer.QT_AreaCode=BSS_CODE.RegionCode,QT_Customer.QT_Area=BSS_CODE.Region
from QT_Customer
inner join INSERTED on QT_Customer.QT_CustID=INSERTED.QT_CustID
--根据QT_Customer.QT_BindPhone前3位或前4位联接,前导或后继空格自己看着处理了,下同:
left join BSS_CODE on BSS_CODE.RegionCode=left(QT_Customer.QT_BindPhone,len(rtrim(BSS_CODE.RegionCode)))
where INSERTED.QT_BindPhone like '[0]%'
end--非0:手机
if exists (select 1 from INSERTED where QT_BindPhone like '[^0]%')
begin
update QT_Customer
set QT_Customer.QT_AreaCode=b.CityCode,QT_Customer.QT_Area=b.CityName
from QT_Customer
inner join INSERTED on QT_Customer.QT_CustID=INSERTED.QT_CustID
--根据QT_Customer.QT_BindPhone前7位联接:
left join BSS_Mobile on BSS_Mobile.MobileHead=left(QT_Customer.QT_BindPhone,7)
where INSERTED.QT_BindPhone like '[^0]%'
endset nocount offgo
if exists (select 1 from INSERTED where QT_BindPhone like '[^0]%')
begin
update QT_Customer
--set QT_Customer.QT_AreaCode=b.CityCode,QT_Customer.QT_Area=b.CityName --此处笔误
set QT_Customer.QT_AreaCode=BSS_Mobile.CityCode,QT_Customer.QT_Area=BSS_Mobile.CityName
-- ...
也可以判断是不是0