Tables’ fields having same values?

Asked By 0 points N/A Posted on -
qa-featured

How can I make sure that a table called Table2 with a field called Field1 will only have the values as in Field1 that are also in another table called Table1 with a field called Field1? How can I force Table2 to contain the values as in Table1?

SHARE
Best Answer by Erin Hall
Best Answer
Best Answer
Answered By 0 points N/A #92757

Tables’ fields having same values?

qa-featured

Hi Nadeemrao,

Use triggers. Triggers are set of SQL statements that automatically executes whenever data are manipulated from operations like insert, delete, or update in the tables. What database server are using? Most database servers support triggers anyway, but one thing I know, MS Access does not support it.

Here is an example of triggers based on your specification. I'm using mysql.

Trigger for every insert on Table1:

delimeter $$

CREATE TRIGGER insert_trigger AFTER INSERT ON table1

FOR EACH ROW

BEGIN

INSERT INTO table2 (field1) VALUES(new.field1);

END$$

delimeter ;

Trigger for every update on Table1:

delimeter $$

CREATE TRIGGER update_trigger AFTER UPDATE ON table1

FOR EACH ROW

BEGIN

UPDATE table2 SET field1 = new.field1 WHERE field1 = old.field1;

END$$

delimeter ;

 Trigger for every delete on Table1:

delimeter $$

CREATE TRIGGER delete_trigger AFTER DELETE ON table1

FOR EACH ROW

BEGIN

DELETE FROM table2 WHERE field1 = old.field1;

END$$

delimeter ;

Hope this helps.

Answered By 0 points N/A #92758

Tables’ fields having same values?

qa-featured

There are several ways to achieve this functionality. I know following two methods:

  • Usage of Foreign key, and
  • Triggers

Foreign key constraint is used so that the child tables will have exactly same value as in parent table prior to insert record in child table. Whereas, triggers are used to occur after some specific event has taken place like a record is inserted or updated in a table. Triggers are of two types like row wise or transaction wise. Row wise means if a query inserts 5 records, trigger will be activated or triggered 5 times. But if transaction wise then trigger will be triggered only once for all 5 rows being inserted.

 

 

Login/Register to Answer

Related Questions