--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] varchar(5),[Value] int) insert [tb] select 'ww',1 union all select 'bb',2select * from [tb] create trigger tri on tb instead of insert as begin insert into tb select id,[Value]=case when id='ww' then 1 else [Value] end from inserted endinsert into tb select 'ww',5 union all select 'wsw',5 select * from tb/* ID Value ----- ----------- ww 1 bb 2 ww 1 wsw 5(4 行受影响) */
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A IF OBJECT_ID('TGR_A') IS NOT NULL DROP TRIGGER TGR_A GO CREATE TABLE A(COL1 INT IDENTITY(1,1),ID VARCHAR(50),[VALUE] INT) INSERT INTO A(ID,[VALUE]) SELECT 'WW',1 UNION ALL SELECT 'BB',2 GO CREATE TRIGGER TGR_A ON A AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM A WHERE ID='WW' AND [VALUE]<>1) UPDATE A SET [VALUE]=1 WHERE ID='WW' AND [VALUE]<>1 END GO INSERT INTO A(ID,[VALUE]) SELECT 'WW',3 UNION ALL SELECT 'BB',4 SELECT * FROM A/* 1 WW 1 2 BB 2 3 WW 1 4 BB 4 */
create trigger tri on tb instead of insert as begin insert into tb select id,[Value]=case when id='ww' then 1 else [Value] end from inserted end
表设计概念错误. 楼主所说的表,应该是一个统计表,而由用户增添ID的表应该是另一个表,在另一个表中创建触发器,可以对新增ID进行统计,统计结果放在这个表中,如: create table usertable(id varchar(10),othercol1 int)--用户要添加ID的表 insert into usertable select 'ww',2544 --第二列是随便添加的,依楼主要求而定 union all select 'bb',8455 union all select 'bb',8745 create table countuser(id varchar(10),countvalue int)--统计表 insert into countuser select 'ww',1 --前表中有一条ID为ww的记录 union all select 'bb',2 go --创建触发器 create trigger tg on usertable after insert as begin if exists(select 1 from countuser a inner join inserted b on a.id=b.id) update countuser set countvalue=countvalue+1 from countuser where id in (select id from inserted) else insert into countcuser select id,1 from inserted end go --测试 insert into usertable select 'ww',547 go select * from countuser go drop table usertable,countuser go /* id countvalue ---------- ----------- ww 2 bb 2 */
呵呵被LZ迷惑了 IF OBJECT_ID('A') IS NOT NULL DROP TABLE A IF OBJECT_ID('TGR_A') IS NOT NULL DROP TRIGGER TGR_A GO CREATE TABLE A(COL1 INT IDENTITY(1,1),ID VARCHAR(50),[VALUE] INT) INSERT INTO A(ID,[VALUE]) SELECT 'WW',1 UNION ALL SELECT 'BB',2 GO CREATE TRIGGER TGR_A ON A instead of INSERT AS BEGIN insert into A select id,[Value]=case when EXISTS(SELECT 1 FROM A WHERE A.ID=INSERTED.ID) then (SELECT MAX([Value]) FROM A WHERE A.ID=INSERTED.ID) else [Value] end from insertedEND GO INSERT INTO A(ID,[VALUE]) SELECT 'WW',3 UNION ALL SELECT 'BB',4 SELECT * FROM A/* 1 WW 1 2 BB 2 3 WW 1 4 BB 2 */
create table usertable(id varchar(10),othercol1 int)--用户要添加ID的表 insert into usertable select 'ww',2544 --第二列是随便添加的,依楼主要求而定 union all select 'bb',8455 union all select 'bb',8745 create table countuser(id varchar(10),countvalue int)--统计表 insert into countuser select 'ww',1 --前表中有一条ID为ww的记录 union all select 'bb',2 go --创建触发器 create trigger tg on usertable after insert as begin if exists(select 1 from countuser a inner join inserted b on a.id=b.id) update countuser set countvalue=countvalue+1 from countuser where id in (select id from inserted) else insert into countuser select id,1 from inserted end go --测试 insert into usertable select 'ww',547 insert into usertable select 'kk',234 go select * from countuser go drop table usertable,countuser go /* id countvalue ---------- ----------- ww 2 bb 2 */
粘贴错误,修改如下: create table usertable(id varchar(10),othercol1 int)--用户要添加ID的表 insert into usertable select 'ww',2544 --第二列是随便添加的,依楼主要求而定 union all select 'bb',8455 union all select 'bb',8745 create table countuser(id varchar(10),countvalue int)--统计表 insert into countuser select 'ww',1 --前表中有一条ID为ww的记录 union all select 'bb',2 go --创建触发器 create trigger tg on usertable after insert as begin if exists(select 1 from countuser a inner join inserted b on a.id=b.id) update countuser set countvalue=countvalue+1 from countuser where id in (select id from inserted) else insert into countuser select id,1 from inserted end go --测试 insert into usertable select 'ww',547 insert into usertable select 'kk',234 go select * from countuser go drop table usertable,countuser go /* id countvalue ---------- ----------- ww 2 bb 2 kk 1 */
create trigger tri on Storage_KCAccount instead of insert as begin insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice from inserted a inner join Storage_KCAccount b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate end
学习高手 我也来个--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] varchar(5),[Value] int) insert [tb] select 'ww',1 union all select 'bb',2select * from [tb]create trigger tri on tb for insert as begin --insert into tb select id,[Value]=case when id='ww' then 1 else [Value] end from inserted update tb set Value=1 where id=(select id from inserted where id='ww') endinsert into tb select 'ww',5 union all select 'wsw',5 select * from tb
上面有误,会产生重复数据. 应为: create trigger tri on Storage_KCAccount instead of insert as begin insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice from inserted a inner join (select distinct GID,Batch,Gstate,price,costprice from Storage_KCAccount where GID=a.GID and Batch=a.Batch and Gstate=a.Gstate) b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate end
create table Storage_KCAccount(DID int,GID int,Batch int,Gstate varchar(10),Number int,price numeric(10,2),costprice numeric(10,2)) insert into Storage_KCAccount select 1,10,555,'KT',200,23.42,28.88 --测试数据 insert into Storage_KCAccount select 2,20,587,'KB',100,18.25,20.33 insert into Storage_KCAccount select 3,10,555,'KT',400,23.42,28.88 go create trigger tri on Storage_KCAccount instead of insert as begin insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice from inserted a inner join ( select distinct t1.GID,t1.Batch,t1.Gstate,t1.price,t1.costprice from Storage_KCAccount t1 inner join inserted t2 on t1.GID=t2.GID and t1.Batch=t2.Batch and t1.Gstate=t2.Gstate ) b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate end go insert into Storage_KCAccount(DID,GID,Batch,Gstate,Number) select 4,10,555,'KT',268 go select * from Storage_KCAccount go drop table Storage_KCAccount /* DID GID Batch Gstate Number price costprice ----------- ----------- ----------- ---------- ----------- --------------------------------------- --------------------------------------- 1 10 555 KT 200 23.42 28.88 2 20 587 KB 100 18.25 20.33 3 10 555 KT 400 23.42 28.88 4 10 555 KT 268 23.42 28.88 */
高手们为什么不这样写插入语句呀 insert to 表 values (...) 而要写成 insert to 已存在的表 select ...
你好 :)create trigger Storage_KCAccount_ADD on Storage_KCAccount AFTER insert as begin insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice from inserted a inner join ( select distinct t1.GID,t1.Batch,t1.Gstate,t1.price,t1.costprice from Storage_KCAccount t1 inner join inserted t2 on t1.GID=t2.GID and t1.Batch=t2.Batch and t1.Gstate=t2.Gstate ) b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate end go我在执行上面sql时,提示消息 8197,级别 16,状态 4,过程 Storage_KCAccount_ADD,第 2 行 对象 'Storage_KCAccount' 不存在,或对此操作无效。要怎么解决呢?
Storage_KCAccount 表是存在的
--创建触发器 create trigger insertvalue on tablename after insert as begin declare @id varchar(20),@value int,@count int select @count=count(*) from inserted where id=(select id from inserted) if @count>=2 begin select top 1 @value=value from tablename update tablename set value=@value where id=(select id from inserted) end end
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(5),[Value] int)
insert [tb]
select 'ww',1 union all
select 'bb',2select * from [tb]
create trigger tri on tb
instead of insert
as
begin
insert into tb select id,[Value]=case when id='ww' then 1 else [Value] end from inserted
endinsert into tb
select 'ww',5 union all
select 'wsw',5
select * from tb/*
ID Value
----- -----------
ww 1
bb 2
ww 1
wsw 5(4 行受影响)
*/
IF OBJECT_ID('TGR_A') IS NOT NULL DROP TRIGGER TGR_A
GO
CREATE TABLE A(COL1 INT IDENTITY(1,1),ID VARCHAR(50),[VALUE] INT)
INSERT INTO A(ID,[VALUE])
SELECT 'WW',1 UNION ALL
SELECT 'BB',2
GO
CREATE TRIGGER TGR_A
ON A
AFTER INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM A WHERE ID='WW' AND [VALUE]<>1)
UPDATE A SET [VALUE]=1 WHERE ID='WW' AND [VALUE]<>1
END
GO
INSERT INTO A(ID,[VALUE])
SELECT 'WW',3 UNION ALL
SELECT 'BB',4
SELECT * FROM A/*
1 WW 1
2 BB 2
3 WW 1
4 BB 4
*/
create trigger tri on tb
instead of insert
as
begin
insert into tb select id,[Value]=case when id='ww' then 1 else [Value] end from inserted
end
楼主所说的表,应该是一个统计表,而由用户增添ID的表应该是另一个表,在另一个表中创建触发器,可以对新增ID进行统计,统计结果放在这个表中,如:
create table usertable(id varchar(10),othercol1 int)--用户要添加ID的表
insert into usertable select 'ww',2544 --第二列是随便添加的,依楼主要求而定
union all select 'bb',8455
union all select 'bb',8745
create table countuser(id varchar(10),countvalue int)--统计表
insert into countuser select 'ww',1 --前表中有一条ID为ww的记录
union all select 'bb',2
go
--创建触发器
create trigger tg on usertable
after insert
as
begin
if exists(select 1 from countuser a inner join inserted b on a.id=b.id)
update countuser set countvalue=countvalue+1 from countuser where id in (select id from inserted)
else
insert into countcuser select id,1 from inserted
end
go
--测试
insert into usertable select 'ww',547
go
select * from countuser
go
drop table usertable,countuser
go
/*
id countvalue
---------- -----------
ww 2
bb 2
*/
里面有五个字段DID(仓位),GID(物料ID),Batch(批号),GState(状态),Number(数量),Price(价格),CostPrice(成本价)数据库的结构已经是这样了,我没法改。DID,GID,Batch,GState四个为主键现在遇到的问题是Number与DID,GID,Batch,GState有关,而Price,CosrPrice只与GID,Batch,GState有关。也就是说只要GID,Batch,GState是一样的,那么Price,CostPrce就应该是一样的了。。但现在每次往数据库里面添加新记录时,只添加DID,GID,Batch,GState,Number这个键的值(Price,CostPrice是通过盘点改变的) 这样就遇到麻烦了,每次一款库存里面有的数据时,都不能自动赋给用户已经盘好的价格。。
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('TGR_A') IS NOT NULL DROP TRIGGER TGR_A
GO
CREATE TABLE A(COL1 INT IDENTITY(1,1),ID VARCHAR(50),[VALUE] INT)
INSERT INTO A(ID,[VALUE])
SELECT 'WW',1 UNION ALL
SELECT 'BB',2
GO
CREATE TRIGGER TGR_A
ON A
instead of INSERT
AS
BEGIN
insert into A select id,[Value]=case when
EXISTS(SELECT 1 FROM A WHERE A.ID=INSERTED.ID)
then (SELECT MAX([Value]) FROM A WHERE A.ID=INSERTED.ID) else [Value] end from insertedEND
GO
INSERT INTO A(ID,[VALUE])
SELECT 'WW',3 UNION ALL
SELECT 'BB',4
SELECT * FROM A/*
1 WW 1
2 BB 2
3 WW 1
4 BB 2
*/
insert into usertable select 'ww',2544 --第二列是随便添加的,依楼主要求而定
union all select 'bb',8455
union all select 'bb',8745
create table countuser(id varchar(10),countvalue int)--统计表
insert into countuser select 'ww',1 --前表中有一条ID为ww的记录
union all select 'bb',2
go
--创建触发器
create trigger tg on usertable
after insert
as
begin
if exists(select 1 from countuser a inner join inserted b on a.id=b.id)
update countuser set countvalue=countvalue+1 from countuser where id in (select id from inserted)
else
insert into countuser select id,1 from inserted
end
go
--测试
insert into usertable select 'ww',547
insert into usertable select 'kk',234
go
select * from countuser
go
drop table usertable,countuser
go
/*
id countvalue
---------- -----------
ww 2
bb 2
*/
create table usertable(id varchar(10),othercol1 int)--用户要添加ID的表
insert into usertable select 'ww',2544 --第二列是随便添加的,依楼主要求而定
union all select 'bb',8455
union all select 'bb',8745
create table countuser(id varchar(10),countvalue int)--统计表
insert into countuser select 'ww',1 --前表中有一条ID为ww的记录
union all select 'bb',2
go
--创建触发器
create trigger tg on usertable
after insert
as
begin
if exists(select 1 from countuser a inner join inserted b on a.id=b.id)
update countuser set countvalue=countvalue+1 from countuser where id in (select id from inserted)
else
insert into countuser select id,1 from inserted
end
go
--测试
insert into usertable select 'ww',547
insert into usertable select 'kk',234
go
select * from countuser
go
drop table usertable,countuser
go
/*
id countvalue
---------- -----------
ww 2
bb 2
kk 1
*/
instead of insert
as
begin
insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice
from inserted a inner join Storage_KCAccount b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate
end
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(5),[Value] int)
insert [tb]
select 'ww',1 union all
select 'bb',2select * from [tb]create trigger tri on tb
for insert
as
begin
--insert into tb select id,[Value]=case when id='ww' then 1 else [Value] end from inserted
update tb set Value=1 where id=(select id from inserted where id='ww')
endinsert into tb
select 'ww',5 union all
select 'wsw',5
select * from tb
应为:
create trigger tri on Storage_KCAccount
instead of insert
as
begin
insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice
from inserted a inner join
(select distinct GID,Batch,Gstate,price,costprice from Storage_KCAccount where GID=a.GID and Batch=a.Batch and Gstate=a.Gstate) b
on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate
end
insert into Storage_KCAccount select 1,10,555,'KT',200,23.42,28.88 --测试数据
insert into Storage_KCAccount select 2,20,587,'KB',100,18.25,20.33
insert into Storage_KCAccount select 3,10,555,'KT',400,23.42,28.88
go
create trigger tri on Storage_KCAccount
instead of insert
as
begin
insert into Storage_KCAccount select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice
from inserted a inner join (
select distinct t1.GID,t1.Batch,t1.Gstate,t1.price,t1.costprice from Storage_KCAccount t1 inner join inserted t2 on t1.GID=t2.GID and t1.Batch=t2.Batch and t1.Gstate=t2.Gstate
) b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate
end
go
insert into Storage_KCAccount(DID,GID,Batch,Gstate,Number) select 4,10,555,'KT',268
go
select * from Storage_KCAccount
go
drop table Storage_KCAccount
/*
DID GID Batch Gstate Number price costprice
----------- ----------- ----------- ---------- ----------- --------------------------------------- ---------------------------------------
1 10 555 KT 200 23.42 28.88
2 20 587 KB 100 18.25 20.33
3 10 555 KT 400 23.42 28.88
4 10 555 KT 268 23.42 28.88
*/
insert to 表 values (...)
而要写成 insert to 已存在的表 select ...
你好 :)create trigger Storage_KCAccount_ADD on Storage_KCAccount
AFTER insert
as
begin
insert into Storage_KCAccount
select a.DID,a.GID,a.Batch,a.Gstate,a.Number,b.price,b.costprice
from inserted a inner join (
select distinct t1.GID,t1.Batch,t1.Gstate,t1.price,t1.costprice from Storage_KCAccount t1 inner join inserted t2 on t1.GID=t2.GID and t1.Batch=t2.Batch and t1.Gstate=t2.Gstate
) b on a.GID=b.GID and a.Batch=b.Batch and a.Gstate=b.Gstate
end
go我在执行上面sql时,提示消息 8197,级别 16,状态 4,过程 Storage_KCAccount_ADD,第 2 行
对象 'Storage_KCAccount' 不存在,或对此操作无效。要怎么解决呢?
--创建触发器
create trigger insertvalue on tablename
after insert
as
begin
declare @id varchar(20),@value int,@count int
select @count=count(*) from inserted where id=(select id from inserted)
if @count>=2
begin
select top 1 @value=value from tablename
update tablename set value=@value where id=(select id from inserted)
end
end