what is the main difference between after trigger and instead trigger.

what is the main difference between after trigger and
instead trigger.

Question Posted / sridhar sahoo

2 Answers
19714 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

what is the main difference between after trigger and instead trigger...

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

what is the main difference between after trigger and instead trigger...

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

Post New Answer