表:Credit_card:UserID varchar(20),Username varchar(20),Bank varchar(10),monetray money,Post_date datime
Log_card:Mending_type,Username,Bank,Monetary,Post_date,Balance
假设有3000元,写一个触发器,记录下每个name的消费记录,并且显示余额.insert into dbo.Credit_card(UserID,Username,Bank,Monetary,Post_date)
values ('111','万万','中国银行','900',GETDATE())
insert into dbo.Credit_card(UserID,Username,Bank,Monetary,Post_date)
values ('112','张三','招商银行','800',GETDATE())要的是这种效果:支出 万万 中国银行 -900 2011-10-7 2100
支出 张三 招商银行 -800 2011-10-7 2200
支出 万万 中国银行 -900 2011-10-7 1200
支出 张三 招商银行 -800 2011-10-7 1400就是3000块钱一直扣,可以有负数
Log_card:Mending_type,Username,Bank,Monetary,Post_date,Balance
假设有3000元,写一个触发器,记录下每个name的消费记录,并且显示余额.insert into dbo.Credit_card(UserID,Username,Bank,Monetary,Post_date)
values ('111','万万','中国银行','900',GETDATE())
insert into dbo.Credit_card(UserID,Username,Bank,Monetary,Post_date)
values ('112','张三','招商银行','800',GETDATE())要的是这种效果:支出 万万 中国银行 -900 2011-10-7 2100
支出 张三 招商银行 -800 2011-10-7 2200
支出 万万 中国银行 -900 2011-10-7 1200
支出 张三 招商银行 -800 2011-10-7 1400就是3000块钱一直扣,可以有负数
解决方案 »
- -----------------------------请教个SQL语句-------------------------------
- aiaiaiaiaiaiai...........................超时超时~
- 如何取得表后面的10条记录
- 这句SQL语句成立吗?(go的含义是什么)
- 向前追踪查询问题
- 关连查询的问题.请大家看看怎么写.谢谢帮忙呀
- 求存储过程!!!!!
- 各位大侠,谁能告诉我为什麽总提示找不到指定的sql server
- 紧急提问:SQL语句中如何定义一个布尔类型!!得到正确回答后,一分钟内结贴!
- SQL 求合计
- 创建用户的相关选项
- 请求各位,SQL2005数据库生成2000脚本错误,帮帮忙解决一下。
select *,
3000-isnull((select sum(monetary) from tb where userid = t.userid),0) as KC
from tb t
--UserID,Username,Bank,Monetary,Post_dateselect *,
3000-isnull((select sum(monetary) from tb where userid = t.userid and post_date<=t.post_date),0) as KC
from tb t
for insert
as
begin
DECLARE @Balance MONEY,@Username varchar(20)
SET @Balance=3000
SELECT @Username=Username FROM INSERTED SELECT @Balance=Balance FROM Log_card AS l WHERE Username=@UsernameIF EXISTS(SELECT 1 FROM INSERTED i WHERE @Balance-monetray )
BEGIN
RAISERROR N'超過信用度'
RETURN
END
INSERT INTO Log_card(Mending_type,Username,Bank,Monetary,Post_date,Balance)
SELECT
Mending_type=CASE WHEN SIGN(Monetary)=1 THEN '支出' ELSE '還款' END,
Username,Bank,monetray=-monetray,Post_date,
Balance=@Balance-monetray
FROM INSERTED i
END
go
CREATE TABLE Credit_card(UserID varchar(20),Username nvarchar(20),Bank nvarchar(10),Monetary money,Post_date DATETIME)
CREATE TABLE Log_card(Mending_type NVARCHAR(20),Username NVARCHAR(20),Bank NVARCHAR(10),Monetary MONEY,Post_date DATETIME,Balance MONEY)
go
Create trigger tr_Credit_card on Credit_card
for insert
as
begin
DECLARE @Balance MONEY,@Username varchar(20)
SET @Balance=3000
SELECT @Username=Username FROM INSERTED SELECT @Balance=Balance FROM Log_card AS l WHERE Username=@UsernameIF EXISTS(SELECT 1 FROM INSERTED i WHERE @Balance-Monetary<0)
BEGIN
RAISERROR 50001 N'超過信用度'
ROLLBACK TRAN
BEGIN TRAN
RETURN
END
INSERT INTO Log_card(Mending_type,Username,Bank,Monetary,Post_date,Balance)
SELECT
Mending_type=CASE WHEN SIGN(Monetary)=1 THEN '支出' ELSE '還款' END,
Username,Bank,monetray=-Monetary,Post_date,
Balance=@Balance-Monetary
FROM INSERTED i
ENDgo
insert into dbo.Credit_card(UserID,Username,Bank,Monetary,Post_date) values (N'111',N'万万',N'中国银行',N'900',GETDATE())
insert into dbo.Credit_card(UserID,Username,Bank,Monetary,Post_date) values (N'112',N'张三',N'招商银行',N'800',GETDATE()) SELECT * FROM Log_card/*
Mending_type Username Bank Monetary Post_date Balance
支出 万万 中国银行 -900.00 2011-10-07 10:59:56.510 2100.00
支出 张三 招商银行 -800.00 2011-10-07 10:59:56.530 2200.00
*/--DROP TABLE Credit_card,Log_card
在应使用条件的上下文(在 ')' 附近)中指定了非布尔类型的表达式。
用4樓方法,3樓是隨手敲的,漏電了 50001 N'超過信用度'
觸發器在SQL2000和2005有區別,05要加 BEGIN TRAN ,不加時提有提示事務-----------
Credit_card.monetray
Log_card.Monetary--提供的這兩個字段名不一致
那取消"超過信用度"判斷就行了Create trigger tr_Credit_card on Credit_card
for insert
as
begin
DECLARE @Balance MONEY,@Username varchar(20)
SET @Balance=3000
SELECT @Username=Username FROM INSERTED SELECT @Balance=Balance FROM Log_card AS l WHERE Username=@Username
INSERT INTO Log_card(Mending_type,Username,Bank,Monetary,Post_date,Balance)
SELECT
Mending_type=CASE WHEN SIGN(Monetary)=1 THEN '支出' ELSE '還款' END,
Username,Bank,monetray=-Monetary,Post_date,
Balance=@Balance-Monetary
FROM INSERTED i
END
CREATE TRIGGER [dbo].[test]
ON [dbo].[Credit_card]
AFTER INSERT
AS
if not exists(select 1 from Log_card a,inserted b where a.Username=b.Username)
begin
insert into Log_card(Mending_type,Username,Bank,Monetary,Post_date,Balance)
Select '支出',Username,Bank,Monetary*(-1),GetDate(),3000-Monetary from inserted
end
else
begin
insert into Log_card(Mending_type,Username,Bank,Monetary,Post_date,Balance)
Select '支出',a.Username,a.Bank,a.Monetary*(-1),GetDate(),b.Balance-a.Monetary from inserted a
left join (select Username,min(Balance) as Balance from Log_card group by Username) b on a.Username=b.Username
END