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