CREATE TABLE products(
--change_id INT IDENTITY PRIMARY KEY, --主键
product_id INT NOT NULL, --产品Id
product_name VARCHAR(255) NOT NULL, --产品名
brand_id INT NOT NULL, --品牌Id
category_id INT NOT NULL, --分类Id
model_year SMALLINT NOT NULL, --那一年的产品
list_price DEC(10,2) NOT NULL, --标价
--updated_at DATETIME NOT NULL, --更新时间
--operation CHAR(3) NOT NULL, --操作方式
);
CREATE TABLE product_audits(
change_id INT IDENTITY PRIMARY KEY, --主键
product_id INT NOT NULL, --产品Id
product_name VARCHAR(255) NOT NULL, --产品名
brand_id INT NOT NULL, --品牌Id
category_id INT NOT NULL, --分类Id
model_year SMALLINT NOT NULL, --那一年的产品
list_price DEC(10,2) NOT NULL, --标价
updated_at DATETIME NOT NULL, --更新时间
operation CHAR(3) NOT NULL, --操作方式
CHECK(operation = 'INS' or operation='DEL')
);
CREATE TRIGGER trg_product_audit
ON products
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO product_audits(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price,
updated_at,
operation
)
SELECT
i.product_id,
product_name,
brand_id,
category_id,
model_year,
i.list_price,
GETDATE(),
'INS'
FROM
inserted i
UNION ALL
SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
GETDATE(),
'DEL'
FROM
deleted d;
END
INSERT INTO products(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price
)
VALUES (
5,
'Test product',
5,
5,
2018,
599
);
select * from products
select * from product_audits