Trigger for 1:m relationship-CodePudding

I have 2 tables: customers and orders. How can I use trigger to create 1:M relationship?

I have next tables

CREATE TABLE Customer(
    customer_id INT GENERATED BY DEFAULT AS IDENTITY,
    customer_name VARCHAR2(50) NOT NULL,
    datachange TIMESTAMP NOT NULL,
    PRIMARY KEY(customer_id)
);


CREATE TABLE Orders(
    order_id INT GENERATED BY DEFAULT AS IDENTITY,
    customer_id INT NOT NULL,   
    PRIMARY KEY(order_id)
);

And I have to do a trigger instead of:

ALTER TABLE Orders add constraint FK_CUSTOMER_ID FOREIGN KEY (customer_id) REFERENCES Customer(customer_id);

CodePudding user response：

here is an example how to create a trigger: https://www.siteground.com/kb/mysql-triggers-use/

but relation is the best solution at this moment:

if there is no specific reason to complicate something, don't complicate it. Use simple and widely used solutions to avoid many problems in the future :-)

ps. I'm not sure if you are using MySQL or some other database because you didn't specify ...

CodePudding user response：

If you look at trigger syntax. It clearly mentions

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

Can't find any trigger on CREATE TABLE.

Can you properly explain why the trigger needs to be fired after you create table? Maybe you are misunderstanding the functions of a trigger