日期:2014-05-18  浏览次数:20551 次

怎样利用触发器实现下面的效果,请各位高手指教了
一、建表代码如下:
Create Table BZDM (
 baozhuangdaima varchar(150) not null,
 baozhuangmingcheng varchar(50) not null,
 banbenhao varchar(50) not null,
 long int not null,
 wit int not null,
 high int not null,
 beizhu varchar not null
)

二:实现效果与条件:
条件1.当baozhuangmingcheng为ZX或ZH时,用触发器怎样实现这一效果
baozhuangdaima自动产生为”banbenhao-long-wit-high-banbenhao”的格式,若为其它,则baozhuangdaima=baozhuangmingcheng
条件2.所有新录入的包装代码不能重复,如果有重复,提示此表中有该代码,不能新增,没有重复,则新增进去
条件3.当更改包装名称的时候,当包装名称为ZX,ZH时,产生的格式与条件一一样;
  baozhuangdaima自动产生为”banbenhao-long-wit-high-banbenhao”的格式,若为其它,则baozhuangdaima=baozhuangmingcheng




------解决方案--------------------
SQL code

--try
create trigger geniuswjt on BZDM
instead of insert,update
as

select @baozhuangmingcheng=baozhuangmingcheng from inserted

if @baozhuangmingcheng in ('ZX','ZH')
    select @baozhuangdaima=banbenhao+'-'+long+'-'+wit+'-'+high+'-'+banbenhao from inserted
else
    set @baozhuangdaima=@baozhuangmingcheng
    
if exists(select 1 from BZDM where baozhuangdaima=@baozhuangdaima)
begin
    print '此表中有该代码,不能新增'
    return
end

if exists(select 1 from inserted) and not exists(select 1 from deleted)
    insert into BZDM
    select @baozhuangdaima,baozhuangmingcheng,banbenha,[long],wit,high,beizhu from inserted
else if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
    delete a from BZDM a,deleted b where a.baozhuangdaima=b.baozhuangdaima
    insert into BZDM
    select @baozhuangdaima,baozhuangmingcheng,banbenha,[long],wit,high,beizhu from inserted
end

------解决方案--------------------
SQL code

--声明下变量记得
declare @baozhuangmingcheng varchar(100)
declare @baozhuangdaima varchar(100)

------解决方案--------------------
探讨
SQL code

--try
create trigger geniuswjt on BZDM
instead of insert,update
as

select @baozhuangmingcheng=baozhuangmingcheng from inserted

if @baozhuangmingcheng in ('ZX','ZH')
select @b……