SQL Server Performance

Triggers Inserts

Discussion in 'SQL Server 2008 General Developer Questions' started by RVG, May 21, 2010.

  1. RVG Member

    Hi,
    I have 3 tables named employee, HR, Payroll they have similar column names in all these tables and they are under same schema and same database. I have a After Trigger on Employee table, called "TRIGEmployee" that notifies me if some inserts any records into the employee table and "commits the transaction". My question here is, If I'm inserting values into employee I need that inserted values to be inserted into HR and Payroll simultaneously.
    For the above scenario to happen, I created a another after trigger named "TrigHR" on Employee table and inside the trigger I'm trying copy the contents from the "select * from inserted" statement to to my HR table but instead I'm not able to do it.
    here is my trigger on HR table:
    Create trigger TrigHR
    on Employee
    for insert
    as
    --Try1
    select * into HR from select * from inserted

    --try 2
    insert into HR select * from inserted"

    commit Tran
    GO
    For Try 1 and Try 2 I'm not able to get the results.Can anyone help me in resolving this issue, please
    Thanks,
    Ravi G Varma.
  2. adnan8t2 New Member

    /*
    01 - Creating tables Employee, HR, Payroll
    02 - Making trigger on Employee table for Insertion
    03 - Inserting row in Employee table
    04 - Testing, Selecting rows from all three tables
    */

    -- 01 - Creating tables Employee, HR, Payroll
    -----------------------------------------------
    -- Tables' Schema
    -----------------------------------------------
    CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    Code:
     [nvarchar](10) NOT NULL,
     [Description] [nvarchar](50) NOT NULL
    ) ON [PRIMARY]
    GO
    CREATE TABLE [dbo].[HR](
     [HRId] [int] IDENTITY(1,1) NOT NULL,
     [Code] [nvarchar](10) NOT NULL,
     [Description] [nvarchar](50) NOT NULL
    ) ON [PRIMARY]
    GO
    CREATE TABLE [dbo].[Payroll](
     [PayrollId] [int] IDENTITY(1,1) NOT NULL,
     [Code] [nvarchar](10) NOT NULL,
     [Description] [nvarchar](50) NOT NULL
    ) ON [PRIMARY]
    GO
    -- 02 - Making trigger on Employee table for Insertion
    -----------------------------------------------
    -- Trigger on Table Employee Insertion
    -----------------------------------------------
    CREATE TRIGGER [dbo].[InsertTrigEmployee]
       ON  [dbo].[Employee]
       For INSERT
    AS 
    BEGIN
     SET NOCOUNT ON;
     -- Insert statements for trigger here
     INSERT INTO HR(Code, [Description])
     SELECT Code, [Description] FROM inserted
     INSERT INTO Payroll(Code, [Description])
     SELECT Code, [Description] FROM inserted
    END
    GO
    -- 03 - Inserting row in Employee table
    /* Insert row in Employee table and you trigger will insert same in HR+Payroll table
    Insert into Employee(Code, [Description])
    Values('A01', 'Some Description')
    */
    -- 04 - Testing, Selecting rows from all three tables
    /* Check all three tables
    Select * From Employee
    Select * From HR
    Select * From Payroll
    */
    -- Regards,
    -- Adnan
    
  3. Adriaan New Member

    Have you checked for error messages?

Share This Page