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 is difference between standardization and normalization?
Are all views updatable ?
What is database architecture? : SQL Server Architecture
Which one is faster delete/truncate? Why?
What is an etl file?
How many databases Microsoft SQL server provides?
What is the difference between lock, block and deadlock? : sql server database administration
What is an Index?
Explain cdc and sql injection?
What is trigger ?
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
Do you know query execution plan?