Asked By
nadeemrao
0 points
N/A
Posted on - 08/09/2011
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?
Tables’ fields having same values?
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
satioa
0 points
N/A
#92758
Tables’ fields having same values?
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.
Â
Â
