Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Task: Avoid user from inserting into Table tblAttendance if Attendance of particular emp is already marked, by checking combination of AttendanceDate and EmpCode.
 
Table: tblAttendance
(EmpCode, AttendanceDate, Status)
 
Problem: Not able to design the trigger as per above requirement in Sql server
Posted 21-Mar-13 20:04pm
Edited 21-Mar-13 20:05pm
v2
Comments
pryashrma at 22-Mar-13 1:06am
   
Tried like this, but it is totally wrong:
CREATE TRIGGER dbo.trgBeforeInsert
ON dbo.tblAttendance
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
-- Insert statements for trigger here
IF EXISTS (SELECT tblAtt.AttendanceDate , tblAtt.EmpCode
FROM dbo.tblAttendance tblAtt
JOIN inserted AS i
ON tblAtt.AttendanceDate=i.AttendanceDate and tblAtt.EmpCode =i.EmpCode
where tblAtt.AttendanceDate=i.AttendanceDate and tblAtt.EmpCode =i.EmpCode
)
BEGIN
RAISERROR ('Attendance Alreday Marked!', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
END
GO
commit;

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try like
CREATE TRIGGER dbo.uniqueUserQuestion 
ON dbo.submit_Answer
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS 
    (
        SELECT 1 
        FROM dbo.submit_Answer T 
        INNER JOIN INSERTED I 
        ON T.user_name = I.user_name 
            AND T.question_id = I.question_id
    )
    BEGIN
        -- Do dupe handling here
        PRINT 'duplicate'
        raiserror('cant submit answer to same question twice')
        return
    END
 
    -- actually add it in
    INSERT INTO
        dbo.submit_Answer
    SELECT
        *
    FROM
        INSERTED I
END
GO
 
  Permalink  
Comments
pryashrma at 22-Mar-13 1:43am
   
Thanks for d help! but problem not yet resolved.. Could U plz help?
 
My Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ps
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER dbo.trgInsertAttendance
ON dbo.tblAttendance
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS
(
SELECT 1
FROM dbo.tblAttendance T
INNER JOIN INSERTED I
ON T.AttendanceDate = I.AttendanceDate
AND T.EmpCode = I.EmpCode
)
BEGIN
-- Do dupe handling here
PRINT 'Duplicate Attendance'
RAISERROR('Cannot Mark Attendance.. As Attendance already Marked for the day!',16,1)
return
END

-- actually add it in
INSERT INTO
dbo.tblAttendance
SELECT
*
FROM
INSERTED I
END
GO
 
but it is generating following ERROR:
Msg 8101, Level 16, State 1, Procedure trgInsertAttendance, Line 28
An explicit value for the identity column in table 'dbo.tblAttendance' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Snehasish Nandy at 22-Mar-13 1:52am
   
Well, the error message basically says it all. You have the following options:
 
Make a column list
OR
make the identity column in dbo.tblAttendance a regular (non-identity) int column (since it's an archive table, why do you need an identity column?).
pryashrma at 22-Mar-13 2:02am
   
Thank You!!! got d point!
The error generated, as AttendanceId is Auto Incrementing so can't insert it explicitly.
Problem Resolved!!!!
Snehasish Nandy at 22-Mar-13 2:14am
   
Ok..if my solution helped you please accept the answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150302.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100