34,590
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO dbo.customers
(
user_id,
name,
sex,
phone,
address,
consumption,
email
)
VALUES
( 1, -- user_id - int
'张三', -- name - varchar(20)
'男', -- sex - char(2)
'13333333333', -- phone - varchar(16)
'地址', -- address - varchar(100)
0.0, -- consumption - float
'1@163.com' -- email - varchar(50)
)
CREATE TRIGGER dbo.booking_TRI
on [dbo].[booking]
AFTER INSERT
as
UPDATE dbo.customers
SET consumption = ISNULL(consumption, 0) + t.total_cost
FROM
(
SELECT Inserted.user_id,
SUM(total_cost) total_cost
FROM Inserted
GROUP BY Inserted.user_id
) t
WHERE t.user_id = dbo.customers.user_id;
GO
INSERT INTO dbo.booking
(
order_id,
order_time,
user_id,
dish_id,
total_cost,
number,
state
)
VALUES
( 1,
GETDATE(),
1,
1,
1*(SELECT 10), -- 这里的10是根据menu表查出来的,楼主没给,我就写死了,这里直接price*number,不用触发器
1,
1
),( 2,
GETDATE(),
1,
1,
2*(SELECT 8), -- 这里的10是根据menu表查出来的,楼主没给,我就写死了,这里直接price*number,不用触发器
1,
1
)
SELECT * FROM booking
SELECT * FROM customers