创建一个触发器,当更新一个表中的列时更新另一个表中的列
我有两个表
Order(id, date, note)
和
Delivery(Id, Note, Date)
我想创建一个触发器,当订单中的日期更新时更新 Delivery 中的日期。
我正在考虑做类似
CREATE OR REPLACE TRIGGER your_trigger_name
BEFORE UPDATE
ON Order
DECLARE
BEGIN
UPDATE Delivery set date = ??? where id = ???
END;
如何获取日期和行 ID?
谢谢
i have two tables
Order(id, date, note)
and
Delivery(Id, Note, Date)
I want to create a trigger that updates the date in Delivery when the date is updated in Order.
I was thinking to do something like
CREATE OR REPLACE TRIGGER your_trigger_name
BEFORE UPDATE
ON Order
DECLARE
BEGIN
UPDATE Delivery set date = ??? where id = ???
END;
How do I get the date and row id?
thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
假设这些是 ORDER 表中名为 DELIVERY_DATE 和 ID 的列,您的触发器应如下所示:
注意 FOR EACH ROW 子句:这是引用各个行中的值所必需的。我使用了 IF 构造来测试是否执行交付更新。如果您的触发器中没有其他逻辑,您可以这样写...
我已经回答了您提出的问题,但是,顺便说一句,我会指出您的数据模型不是最优的。正确规范化的设计将仅在一个表上保留 DELIVERY_DATE:DELIVERY 似乎是它的逻辑位置。
Assuming these are columns on your ORDER table called DELIVERY_DATE and ID your trigger should look something like this:
Note the FOR EACH ROW clause: that is necessary to reference values from individual rows. I have used an IF construct to test whether to execute the UPDATE on Delivery. If you have no other logic in your trigger you could write it like this...
I have answered the question you asked but, as an aside, I will point out that your data model is sub-optimal. A properly normalized design would hold DELIVERY_DATE on only one table: DELIVERY seems teh logical place for it.
使用 OLD 和 NEW 绑定变量。 OLD 引用更改之前正在更新的行或列; NEW 在更改后引用它。
您可以修改
REFERENCING
子句来为绑定变量指定不同的名称。您也可以将OLD 包含为
。示例:如果您不想更改“old”和“new”的默认名称,则可以完全省略
REFERENCING
子句。Use the OLD and NEW bind variables. OLD references the row or column being updated before the change is made; NEW references it after the change.
You can modify the
REFERENCING
clause to give your bind variables different names. You can includeOLD as <name>
too. Example:If you don't want to change the default names of "old" and "new", you can leave out the
REFERENCING
clause completely.触发器中有一个隐式的新旧引用,其形式为:
REFERENCING OLD AS OLD NEW AS NEW
您可以写入 :NEW 值,但不能写入 :OLD 值。
模板:
There is an implicit new and old reference in the trigger in the form of:
REFERENCING OLD AS OLD NEW AS NEW
You can write to the :NEW value but not to the :OLD value.
Template:
每当需要这种触发器时,请仔细查看您的设计。真的需要单独的交货记录吗?一个订单真的有超过1次送货吗?
触发器看起来不错,但它们确实很快就会把事情搞砸。
Whenever there is a need for this kind of trigger, have a good look at your design. Is there really a need for a separate delivery record? Does an order really have more than 1 delivery ?
Triggers seem nice but they do tend to mess things up pretty quickly.