what is the main difference between after trigger and
instead trigger.
Answer Posted / rajneesh hajela
ALTER VIEW Employee AS
SELECT P.SSN as SSN FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do
an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in
PersonDuplicates table.
INSERT INTO PersonDuplicates
```````````````````````````````
FROM inserted
-- Check for duplicate Employee. If no there is duplicate,
do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there
will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
syntex can be wrong u see only logic
Instead Of Trigger fires an operation instead of performing
user specified operation.
u can create instead of trigger on views but u can not
create after triggers on views
Rajneesh Hajela
Is This Answer Correct ? | 3 Yes | 7 No |
Post New Answer View All Answers
What is row_number function?
How to enable/disable indexes?
Mention the different types of triggers?
Is there any difference between primary key and unique with the not null condition?
Who developed sql server?
How does the report manager work in SSRS?
Explain indexed views?
How can i Relate Tables in SSIS
whats new about truncate in sql server 2008?
What types of replication are supported in sql server?
What are the difference between “where” and “having” clause in sql server?
What is proper subset of candidate key?
What are recommended options to be used while using db mirroring? : sql server database administration
Can I use sql azure as a backup with log shipping or database mirroring?
Can a unique index be created on a column, which contains null?