SQL Server

查询 card 的记录为两次以上的 card，记录数:
select count(card), card from TableName group by card having count(card) > 1
级联更新，级联删除:
ColumnName type not null constraint FK_Name foreign key(ColumnName) references PrimaryTable(ColumnName) on update/delete cascade
自动计算列:
create table TableName
(
 ID int,
 Price decimal(10,2) not null,
 Number int not null,
 Total as Price * number  -- Total 不存储值。
)
insert into TableName values(1, 100, 123)
insert into TableName values(1, 100, 123)
insert into TableName values(2, 200, 456)
insert into TableName values(2, 200, 456)
select ID, Total=SUM(Total) from TableName where ID = 1 group by ID
修改现有商品数量:
begin
begin tran
declare @ForeignError int, @PrimaryError int
insert into ForeignTable values(@ID, @Number)
select @ForeignError=@@Error              -- @@Error: 返回执行的上一个 Transact-SQL 语句的错误号。
update PrimaryTable set Number=Number ± @Number where ID=@ID --进货时用加号。
select @PrimaryError=@@Error
if (@ForeignError = 0 and @PrimaryError = 0)
 commit tran
else
 rollback tran
end
begin
begin tran
declare @ForeignError int, @PrimaryError int, @NewNumber int
update ForeignTable set Number=@Number, @NewNumber=@Number - Number where ID=@ID
select @ForeignError=@@Error
update PrimaryTable set Number=Number ± @NewNumber where ID=@ID --进货时用加号。
select @PrimaryError=@@Error
if (@ForeignError = 0 and @PrimaryError = 0)
 commit tran
else
 rollback tran
end