CREATE TRIGGER TRIGGERNAME ON TABLENAME
FOR INSERT
AS
UPDATE TABLENAME SET 数量=数量+A.数量 FROM INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期
FOR INSERT
AS
UPDATE TABLENAME SET 数量=数量+A.数量 FROM INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期
CREATE TRIGGER TRIGGERNAME ON TABLENAME
FOR INSERT
AS
IF (SELECT COUNT(1) FROM TABLENAME ,INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期 ) >0
BEIN
UPDATE TABLENAME SET 数量=数量+A.数量 FROM INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期 DLETE TABLENAME WHERE EXISTS (SELECT * FROM INSERTED)
END
create table t1(卡种 varchar(20),数量 int,日期 datetime)select * from t1
/*
卡种 数量 日期
-------------------- ----------- -----------
(所影响的行数为 0 行)
*/
go
create TRIGGER TRIGGERNAME ON t1
INSTEAD OF INSERT
AS
UPDATE a SET 数量=a.数量+b.数量 FROM t1 a,INSERTED b
WHERE a.卡种=b.卡种 AND a.日期=b.日期
insert t1
select * from inserted a where not exists(select 1 from t1 b where a.卡种=b.卡种 AND a.日期=b.日期)
go
insert t1
select 'IC', 20, '2005-9-18' union all
select 'IP', 30, '2005-9-18'
select * from t1
go
/*
卡种 数量 日期
-------------------- ----------- ----------------------------
IC 20 2005-09-18 00:00:00.000
IP 30 2005-09-18 00:00:00.000(所影响的行数为 2 行)
*/
insert t1
select 'IC', 20, '2005-9-18' union all
select 'IP', 30, '2005-9-18'
select * from t1
go
/*
卡种 数量 日期
-------------------- ----------- ------------------------------------------------------
IC 40 2005-09-18 00:00:00.000
IP 60 2005-09-18 00:00:00.000(所影响的行数为 2 行)*/
drop table t1
INSTEAD OF INSERT
AS
IF (SELECT COUNT(1) FROM TABLENAME ,INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期 ) >0
BEIN
UPDATE TABLENAME SET 数量=数量+A.数量 FROM INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期END
ELSE
INSERT INTO TABLENAME SELECT * FROM INSERTED
INSTEAD OF INSERT
AS
IF (SELECT COUNT(1) FROM TABLENAME ,INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期 ) >0
BEGIN
UPDATE TABLENAME SET 数量=TABLENAME.数量+A.数量 FROM INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期END
ELSE
INSERT INTO TABLENAME SELECT * FROM INSERTED
SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期 INTO TABLENAME
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期GO CREATE TRIGGER TRIGGERNAME ON TABLENAME
INSTEAD OF INSERT
AS
IF (SELECT COUNT(1) FROM TABLENAME ,INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期 ) >0
BEGIN
UPDATE TABLENAME SET 数量=TABLENAME.数量+A.数量 FROM INSERTED A
WHERE TABLENAME.卡种=A.卡种 AND TABLENAME.日期=A.日期END
ELSE
INSERT INTO TABLENAME SELECT * FROM INSERTEDGO
INSERT INTO TABLENAME SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAME
--IC 40 2005-09-18 00:00:00.000
--IP 60 2005-09-18 00:00:00.000
GO
INSERT INTO TABLENAME SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAME
--IC 60 2005-09-18 00:00:00.000
--IP 90 2005-09-18 00:00:00.000GO
INSERT INTO TABLENAME SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAME
--IC 80 2005-09-18 00:00:00.000
--IP 120 2005-09-18 00:00:00.000GO
INSERT INTO TABLENAME SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAME
--IC 100 2005-09-18 00:00:00.000
--IP 150 2005-09-18 00:00:00.000GO
INSERT INTO TABLENAME SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAME
--IC 120 2005-09-18 00:00:00.000
--IP 180 2005-09-18 00:00:00.000GO
INSERT INTO TABLENAME SELECT 'IC' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IP' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAME
--IC 140 2005-09-18 00:00:00.000
--IP 210 2005-09-18 00:00:00.000GOINSERT INTO TABLENAME SELECT 'IA' 卡种 , 20 数量, CAST('2005-9-18' AS DATETIME) 日期
UNION SELECT 'IB' 卡种 , 30 数量, CAST('2005-9-18' AS DATETIME) 日期
GO
SELECT * FROM TABLENAMEDROP TABLE TABLENAME--如果有错误请指正
on A
instead of insert
asdeclare @tb table
(
ID int identity,
[卡种] varchar(10),
[数量] int,
[日期] varchar(10)
)insert @tb([卡种],[数量],[日期])
select [卡种],[数量],[日期] from inserteddeclare @i int
select @i=1while exists(select 1 from @tb where ID=@i)
begin
if exists(select 1
from @tb T
join A on T.[卡种]=A.[卡种]
and T.[数量]=A.[数量]
and T.[日期]=A.[日期]
where T.ID=@i
)
begin
update A
set [数量]=A.[数量]+T.[数量]
from A
join @tb T on T.[卡种]=A.[卡种]
and T.[数量]=A.[数量]
and T.[日期]=A.[日期]
where T.ID=@i
end
else
begin
insert A([卡种],[数量],[日期])
select [卡种],[数量],[日期] from @tb where ID=@i
end set @i=@i+1
endif @@error<>0
begin
rollback tran
raiserror('操作失败,错误号为%d',12,1,@@error)
end