I am working on setting up a database and have created tables that cascade out like using the cascade on update for the foreign key. I have two tables being worked with to try to solve this. My tables are like this:

create table Item(Item int(4) not null, EquipName varchar(20), Equip int(4) 
not null, primary key(Item, Equip))

create table Cross(Time timestamp default now(), Cross varchar(10) default 
'null', Item int(4) not null, Equip int(4) not null, Stop varchar(20) default 
'null', foreign key(Item, Equip) references Item(Item, Equip) on update 
cascade);

So I want to be able to enter data into the Item table and have it be automatically put into Cross table where those values are cascaded to. This is why Cross and StopCount have a default value of null.

For example insert into Item values(5, fan, 4); I would like Cross to be populated in the FK spots automatically to what information was entered into Item.

1

There are 1 answers

1
piotrm On BEST ANSWER

You can use a trigger to autofill another table.

DELIMITER $$
CREATE TRIGGER init_cross AFTER INSERT ON item
  FOR EACH ROW
  BEGIN
    INSERT INTO `cross`(item,equip) VALUES( NEW.item, NEW.equip );
  END;
$$
DELIMITER ;