Database

<italic>Work hard and a bit of luck is the key to success.

<italic>Work hard and a bit of luck is the key to success.

Customers
ID_customer Name
    1       Luis Alonso Ramos
    2       John Smith

Addresses
ID_address ID_customer Street                City     LivesHereSince
    1           1      123 CodeProject St.     1         1/1/2001
    2           1      22  Maunder Ave.        2         1/1/2002
    3           2      2222 5th Ave.           1         1/1/1999

Cities
ID_city   Name
   1      Los Angeles
   2      Beverly Hills

ID_customer Name              City
    1     Luis Alonso Ramos   Beverly Hills

SELECT C.ID_customer, C.Name, Cities.Name AS City
FROM 
    Customers C
INNER JOIN
    Addresses A ON C.ID_customer = A.ID_customer
INNER JOIN
(
    SELECT ID_customer, MAX(LivesHereSince) AS LastMove
    FROM Addresses
    GROUP BY ID_customer
) A2
    ON A.ID_customer = A2.ID_customer AND A.LivesHereSince = A2.LastMove
INNER JOIN
    Cities ON A.City = Cities.ID_city

SELECT C.ID_customer, C.Name, AC.City
FROM
    Customers C
LEFT JOIN
(
    SELECT
        A.ID_customer, Cities.Name AS City
    FROM     
        Addresses A
    INNER JOIN
    (
        SELECT ID_customer, MAX(LivesHereSince) AS LastMove
        FROM Addresses
        GROUP BY ID_customer
    ) A2
        ON A.ID_customer = A2.ID_customer AND A.LivesHereSince = A2.LastMove
    INNER JOIN
        Cities ON A.City = Cities.ID_city
) AC ON C.ID_customer = AC.ID_customer

CREATE  TRIGGER trigUpdateCustomersCurrentAddress
ON Addresses
AFTER INSERT AS
    UPDATE Customers
    SET CurrentAddress = inserted.ID_address
    FROM inserted
    WHERE Customers.ID_customer = inserted.ID_customer

CREATE TRIGGER trigUpdateCustomersCurrentAddress
ON Addresses
AFTER INSERT AS
    UPDATE Customers
    SET CurrentAddress = inserted.ID_address
    FROM inserted
    INNER JOIN Addresses
        ON inserted.ID_customer = Addresses.ID_Customer AND
           inserted.LivesHereSince > Addresses.LivesHereSince
    WHERE Customers.ID_customer = inserted.ID_customer

UPDATE Customers
SET CurrentAddress = A.ID_address
FROM
    Addresses A
INNER JOIN
(
    SELECT ID_customer, MAX(LivesHereSince) AS LastMove
    FROM Addresses
    GROUP BY ID_customer
) A2
    ON A.ID_customer = A2.ID_customer AND A.LivesHereSince = A2.LastMove    
WHERE
    Customers.ID_customer = A.ID_customer

<br />
OleDbCommand cmdUpdate = new OleDbCommand(SQLCOMMAND);<br />
OleDbCommand cmdInsert = new OleDbCommand(SQLCOMMAND);<br />
<br />
myAdapter.UpdateCommand = cmdUpdate;<br />
myAdapter.InsertCommand = cmdInsert;<br />
<br />