How Can SQL Server Triggers Ensure Data Integrity When Inserting Employee Records?

Inserting New Employee Records with SQL Server Triggers

In SQL Server 2008, managing relationships between tables is crucial for data integrity. Consider the case of two tables, Employee and EmployeeResult. To ensure that each row inserted into EmployeeResult has a corresponding record in Employee, a simple INSERT trigger is required.

Trigger Definition

The INSERT trigger, named trig_Update_Employee, should perform the following task:

• Iterate over each row inserted into EmployeeResult
• Check if an employee with the same name and department exists in Employee
• If the employee does not exist, insert a new record into Employee with the corresponding values

Trigger Implementation

To implement this trigger, utilize the inserted logical table that provides access to the rows being inserted. The following trigger definition satisfies the stated requirements:

CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
Begin
    Insert into Employee (Name, Department) 
    Select Distinct i.Name, i.Department 
    from Inserted i
    Left Join Employee e
    on i.Name = e.Name and i.Department = e.Department
    where e.Name is null
End

Trigger Functionality

• The trigger is executed whenever a row is inserted into EmployeeResult.
• The inserted table provides a way to access the inserted row data.
• The trigger uses a LEFT JOIN to check if an employee with the same name and department exists in the Employee table.
• If no matching employee is found, a new record is inserted into Employee with the corresponding information from the inserted row.
• The trigger ensures that the Employee table contains all the necessary employee information to maintain data consistency.

The above is the detailed content of How Can SQL Server Triggers Ensure Data Integrity When Inserting Employee Records?. For more information, please follow other related articles on the PHP Chinese website!