none
Triggers to insert the record in a table RRS feed

  • Question

  • I have two table 1. Holiday 2. Attendance.

    When I insert the record    in holiday table for his  advance holiday   with empid, the same time I want to insert it attendance table  automatically for the same date using a trigger

    Insert into attendance (empid,date,holiday) values (20078,07/10/2014,1). If holiday column value 1 represent holiday marked,  0 represent  holiday not marked. The same thing can happen vice versa

    If employee mark his current attendance as  holiday through attendance,  it should   be  inserted into holiday table  automatically using triggers.

    Insert into  Holiday (empid,date,holiday) values (20078,06/08/2014,1). If holiday column value 1 represent holiday marked,  0 represent  holiday not marked. The same thing can happen vice versa

    Please I am looking for your help , how I can make it using triggers  to insert both table  in two different ways of options.

    Regards

    Pol


    polachan

    Wednesday, August 6, 2014 9:36 AM

Answers

  • Hi polachan,

    According to your description, if you want to synchronize the data between the holiday table and the attendance table while inserting records into holiday table, you need to create a trigger on the holiday table, please try the following syntax.

    use  <databasename>
    go
    
    create trigger Tr_holiday
    on holiday 
    for insert
    as
    
    declare @empid varchar(20),        
    @date datetime, 
    @holiday int
    select @empid = empid, @date=date, @holiday=holiday from inserted
    
    declare @qty int 
    select @qty =count(*) from attendance where empid=@empid and date=@date and holiday=@holiday
    
    if @qty<1 
    begin
      insert into attendance
      select i.empid,
             i.date,
             i.holiday
        from inserted i
    end


    Meanwhile, if you want to insert the record into the holiday table when holiday is updated to 1 in the attendance table, you need to create another trigger on the attendance table, please try the following syntax.

    use  <databasename>
    go
    
    create trigger Tr_attendance
    on attendance 
    for update
    
    as
    declare @holiday int
    if update (holiday)
    begin
    select @holiday=holiday from inserted 
    if @holiday=1
    begin
      insert into dbo.holiday
      select empid,
             date,
            holiday
        from inserted 
    end
    end

    For more details about creating triggers in SQL Server, please review this article: CREATE TRIGGER (Transact-SQL).


    Thanks,
    Lydia Zhang






    Thursday, August 7, 2014 9:25 AM
    Moderator