How to insert a single row only in a table

 Posted by V-rajbog on 3/23/2011 | Category: Sql Server Interview questions | Views: 2928 | Points: 40
Answer:

using INSTEAD OF trigger, validates row counts of both original and INSERTED tables

CREATE TRIGGER trInsertEmployee ON Employee

INSTEAD OF INSERT
AS
BEGIN
IF EXISTS
(
SELECT *
FROM dbo.Employee
)
BEGIN
PRINT 'Table contains one record.'
RETURN
END
ELSE
BEGIN
IF ( SELECT COUNT(*) FROM INSERTED) > 1
BEGIN
PRINT 'Can not insert more than one record.'
RETURN
END
ELSE
BEGIN
INSERT INTO dbo.Employee(EmpID, EmpFullName, DeptID, GrossSalary)
SELECT TOP 1 EmpID, EmpFullName, DeptID, GrossSalary
FROM INSERTED
END
END
END


Source: Own | Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response

More Interview Questions by V-rajbog