what is the main difference between after trigger and
instead trigger.
Answers were Sorted based on User's Feedback
Answer / durga prasad
After Trigger fires an operation after any
insert,update,delete operations performed on a table.
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
Is This Answer Correct ? | 38 Yes | 5 No |
Answer / 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 |
What are the properties of the transaction?
Can we write ddl in trigger?
What is self contained multi valued query?
what is curser.
What is dml command?
What are the requirements for sql server 2016?
can anyone explain me the concept of Serialization in Detail and Clear? plz its urgent i have interview on friday (15th feb)
What is query optimizer in sql server?
What are constraints? Explain different types of constraints?
26 Answers Emsang, Kendriya Vidyalaya(Kvs), Polaris, Wipro,
How to restore performance issues and how to check?
What do you understand by SQL*Net?
Normalization and denormalization