Tables’ fields having same values?

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?

• Status: Open
• Question Views: 804
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by Erin Hall

Go To Solution

Best Answer

Best Answer

Answered By Erin Hall 0 points N/A #92757

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.

About Erin Hall

Questions
0

Answers
6

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 08/13/2011
• Question Category: MS SQL Server

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.

 

 

About satioa

Questions
1

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 08/19/2011
• Question Category: MS SQL Server