Create Trigger for Restricting Duplicate Entries

Question

0.00/5 (No votes)

See more:

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 19:04pm

pryashrma

Updated 21-Mar-13 19:05pm

v2

Add a Solution

Comments

pryashrma 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Code-Hunt · Accepted Answer · 2013-03-21T19:08:00

Solution 1

Try like

SQL

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

Posted 21-Mar-13 19:08pm

Code-Hunt

Comments

pryashrma 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.

[no name] 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 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!!!!

[no name] 22-Mar-13 2:14am

Ok..if my solution helped you please accept the answer