How to Write an INSERT Trigger in SQL Server 2008 to Ensure Employee Data Integrity?

Writing an INSERT Trigger in SQL Server 2008: Triggering Employee Information Insertion

In SQL Server 2008, you can utilize triggers to perform specific actions when data is modified in a table. One such scenario is ensuring the existence of employee information in the Employee table upon insertion into the EmployeeResult table.

To create a trigger that inserts employee records when they are missing in the Employee table:

CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
BEGIN
  -- Retrieve the inserted row data
  SELECT *
  INTO #InsertedRows
  FROM INSERTED;

  -- Check if the inserted employee exists in the Employee table
  SELECT COUNT(*)
  INTO #EmployeeCount
  FROM Employee
  WHERE Name IN (SELECT Name FROM #InsertedRows)
    AND Department IN (SELECT Department FROM #InsertedRows);

  -- Insert missing employee records
  IF #EmployeeCount < 1
  BEGIN
    -- Dynamically generate the INSERT statement based on the inserted rows
    DECLARE @InsertStatement NVARCHAR(MAX) = 'INSERT INTO Employee (Name, Department) VALUES ';
    SELECT @InsertStatement += '("" + Name + "", ""' + Department + '"")'
    FROM #InsertedRows;

    -- Execute the dynamic INSERT statement
    EXEC sp_executesql @InsertStatement;
  END;
END;

This trigger utilizes the INSERTED pseudo-table to access the inserted data. It first checks if the employee name and department pair already exists in the Employee table using a COUNT(*) query. If they don't, it dynamically generates an INSERT statement using the VALUES clause based on the inserted rows. Finally, it executes the INSERT statement to insert the missing employee information.

By implementing this trigger, you can ensure that every EmployeeResult insertion results in the existence of corresponding employee data in the Employee table, ensuring data integrity and completeness.

The above is the detailed content of How to Write an INSERT Trigger in SQL Server 2008 to Ensure Employee Data Integrity?. For more information, please follow other related articles on the PHP Chinese website!