Trigger - Table - Pre insert to check duplicate values

Question

I am using Sql Server 2008 R2.

I have a table -   TS_Time_Sheet_Details--

Fields are  : ( in addition to other fields)

1. Emp_no 
2. Date,
3. From_time
4. To_Time

i want a trigger ( or any other way) - when there is  from_Time & To _Time is over lapped -  trigger should failed.

Example of overlapping timing:

Say : one row : Emp_No = 10, Date is 29-12-2015 , from_Time =8 , To_Time =11

When user insert another rows

Emp_no 10, date is 29-12-2015 (same date,same employee) , from_time =9  and to_time =10

Can you please guide the correct step.

I am very new to SQL server after version 7.

Thanks

Answer 1

try this..

CREATE TRIGGER MyTrigger ON dbo.TS_Time_Sheet_Details
AFTER INSERT
AS

if exists ( select * from TS_Time_Sheet_Details t 
    inner join inserted i on i.Emp_no=t.Emp_no and i.date=t.date)  
begin
    rollback
    RAISERROR ('Duplicate Data in TS_Time_Sheet_Details', 16, 1);
end
go

--

---

Please click Mark As Answer if my post helped.

Answer 2

Hi

Thanks foryour reply.

After applying above trigger, i am unable to add any row.

I just truncated the table . Trying to insert first row, getting error.

 

Answer 3

Then why did you mark it as an answer?  Yes, the posted trigger was flawed.  I also suggest your table definition is flawed.  Why?  What happens when the time period of a particular row crosses midnight?  So, person x works on December 30 from 23:15 to 06:15 (the next morning).  What is recorded?  What happens when person x works a split shift? An additional flaw (at least in what you posted - poorly, I'll add) is that there doesn't appear to be a primary key for the table.

So the first step is to identify the primary key of the table.  Without that knowledge, one cannot write the trigger.  Why?  Because you will need to join rows from the virtual inserted table to the physical table to find the duplicates while avoiding the self-joins.

Answer 4

And when I mention "primary key", I mean the natural primary key. An identity will help with the self-join avoidance, but you still need to enforce the natural key.

Answer 5

And finally a testable script.  Note the constraints.

set nocount on;

create table dbo.timesheet (
timesheetid int identity(1,1) not null, 
empno int not null, 
tsdate date not null, 
tsfrom time not null, 
tsto time not null, 
constraint pkx primary key (empno, tsdate, tsfrom), 
constraint cxy check (tsfrom < tsto) 
);
go

create trigger x on dbo.timesheet after insert 
as
begin
if exists (select * from inserted as ins inner join dbo.timesheet as main 
   on ins.empno = main.empno and ins.tsdate = main.tsdate 
   and ins.tsfrom <= main.tsto
   and ins.tsto >= main.tsfrom 
   --and <avoid self join logic here>
   and ins.timesheetid <> main.timesheetid
   )
   begin;
      RAISERROR('Overlap error', 11,-1) WITH SETERROR;
	  rollback transaction;
	  return;
  end;

end;
go


insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '8:00', '12:00');
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '8:00', '12:00');  -- exact dupicate
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '7:00', '6:59');  -- check from < to failure
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (2, '20151230', '8:00', '12:00'); -- different emp, same date and time
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151231', '8:00', '12:00'); -- same emp, different date, same times 
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '12:01', '12:45'); -- no overlap
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '12:30', '12:35'); --  overlap
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '7:30', '14:35'); --  overlap
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '7:00', '8:00'); --  overlap
go
insert dbo.timesheet (empno, tsdate, tsfrom, tsto) values (1, '20151230', '12:50', '23:00'); --  no overlap
go
select * from dbo.timesheet;
go

drop table dbo.timesheet;
go

Answer 6

Hi gselvam1,

What about the new answer? If you have already figured out your own solution, would you mind sharing with us? Your kind answer will be highly appreciated.

---

Sam Zha
TechNet Community Support