create or replace function InsertSale() returns trigger as $$ begin insert into Bookstock values(new.Sale_ID,new.Sale_Date,'出库',new.Sale_Number,new.Book_ISBN); update Book set Book_Stock = Book_Stock - new.Sale_Number where Book_ISBN = new.Book_ISBN; return new; end; $$ language plpgsql;这段代码中的Book表无法更新
时间: 2023-06-24 12:06:43 浏览: 43
可能是因为你的代码中没有对Book表进行触发器的设置,导致Book表的更新操作无法被触发。你可以尝试在Book表上添加一个触发器,使其在Sale表进行插入操作时自动更新Book表的库存信息,例如:
```
CREATE OR REPLACE FUNCTION UpdateBookStock() RETURNS TRIGGER AS $$
BEGIN
UPDATE Book SET Book_Stock = Book_Stock - NEW.Sale_Number WHERE Book_ISBN = NEW.Book_ISBN;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sale_insert AFTER INSERT ON Sale
FOR EACH ROW EXECUTE FUNCTION UpdateBookStock();
```
这样,当Sale表进行插入操作时,触发器会自动更新Book表的库存信息。
相关问题
CREATE TRIGGER `af-del` AFTER UPDATE ON `employee_info` FOR EACH ROW BEGIN INSERT INTO departinfo_info VALUES (NEW.Id, NEW.name, NEW.Deparment,NEW.Entry,NEW.LEVEL); END;
这是一个 MySQL 数据库的触发器,意思是在 `employee_info` 表中的数据被更新后,自动将更新后的数据插入到 `departinfo_info` 表中。具体解释如下:
- `CREATE TRIGGER`: 创建触发器的语法。
- `af-del`: 触发器的名称。
- `AFTER UPDATE`: 触发器的触发时机,即在更新操作之后触发。
- `ON employee_info`: 触发器所绑定的表,即 `employee_info` 表。
- `FOR EACH ROW`: 对于每一行触发器都会执行一次。
- `BEGIN`: 触发器的语句块开始。
- `INSERT INTO departinfo_info`: 将数据插入到 `departinfo_info` 表中。
- `VALUES (NEW.Id, NEW.name, NEW.Deparment,NEW.Entry,NEW.LEVEL)`: 插入的值,即 `employee_info` 表中被更新后的 `Id`、`name`、`Deparment`、`Entry`、`LEVEL` 字段的值。
- `END`: 触发器的语句块结束。
CREATE TRIGGER update_invoice_complex AFTER UPDATE ON t_sfi_inter_invoice FOR EACH ROW BEGIN INSERT INTO t_sfi_inter_bus_complex (OPER_TIME, OPERATION_TYPE, OLD_OBJECT,NEW_OBJECT) VALUES (NOW(), 'update',JSON_OBJECT(OLD.ID,OLD_OINVOICE_NO,OLD.SUM,OLD.FACT_SUM,OLD.STATUS,OLD.BUSINESS_ID,OLD.REINVOICE,OLD.IS_PRINTED,OLD.PRINT_USER_ID,OLD.PRINT_USER_NAME,OLD.PRINT_DATE,OLD.INVOICE_TYPE,OLD.INVOICE_STATUS,OLD.TOMB_CERTIFICATE),JSON_OBJECT(NEW.ID,OINVOICE_NO,NEW.SUM,NEW.FACT_SUM,NEW.STATUS,NEW.BUSINESS_ID,NEW.REINVOICE,NEW.IS_PRINTED,NEW.PRINT_USER_ID,NEW.PRINT_USER_NAME,NEW.PRINT_DATE,NEW.INVOICE_TYPE,NEW.INVOICE_STATUS,NEW.TOMB_CERTIFICATE)); END;优化一下
可以尝试将触发器中的JSON_OBJECT函数拆分成单独的变量,以提高可读性和易维护性。同时,将INSERT语句中的字段名改为明确的列名,而不是使用通配符。建议修改后的触发器代码如下:
```
CREATE TRIGGER update_invoice_complex
AFTER UPDATE ON t_sfi_inter_invoice
FOR EACH ROW
BEGIN
DECLARE old_invoice_json JSON;
DECLARE new_invoice_json JSON;
SET old_invoice_json = JSON_OBJECT('id', OLD.ID,
'oinvoice_no', OLD.OINVOICE_NO,
'sum', OLD.SUM,
'fact_sum', OLD.FACT_SUM,
'status', OLD.STATUS,
'business_id', OLD.BUSINESS_ID,
'reinvoice', OLD.REINVOICE,
'is_printed', OLD.IS_PRINTED,
'print_user_id', OLD.PRINT_USER_ID,
'print_user_name', OLD.PRINT_USER_NAME,
'print_date', OLD.PRINT_DATE,
'invoice_type', OLD.INVOICE_TYPE,
'invoice_status', OLD.INVOICE_STATUS,
'tomb_certificate', OLD.TOMB_CERTIFICATE);
SET new_invoice_json = JSON_OBJECT('id', NEW.ID,
'oinvoice_no', NEW.OINVOICE_NO,
'sum', NEW.SUM,
'fact_sum', NEW.FACT_SUM,
'status', NEW.STATUS,
'business_id', NEW.BUSINESS_ID,
'reinvoice', NEW.REINVOICE,
'is_printed', NEW.IS_PRINTED,
'print_user_id', NEW.PRINT_USER_ID,
'print_user_name', NEW.PRINT_USER_NAME,
'print_date', NEW.PRINT_DATE,
'invoice_type', NEW.INVOICE_TYPE,
'invoice_status', NEW.INVOICE_STATUS,
'tomb_certificate', NEW.TOMB_CERTIFICATE);
INSERT INTO t_sfi_inter_bus_complex (OPER_TIME, OPERATION_TYPE, OLD_OBJECT, NEW_OBJECT)
VALUES (NOW(), 'update', old_invoice_json, new_invoice_json);
END;
```