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)

  Print Answers RSS
0 Maciej Los 290
1 OriginalGriff 275
2 Sergey Alexandrovich Kryukov 195
3 BillWoodruff 140
4 ProgramFOX 140
0 OriginalGriff 6,524
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,218
3 Manas Bhardwaj 4,657
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
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