Interested to Buy Any Domain ? << Click Here >> for more details...
what is the main difference between after trigger and
instead trigger.
- 2 Answers
- 19713 Views
- I also Faced
- E-Mail Answers
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
how to do partition in sqlserver
Why the trigger fires multiple times in single login?
Why would you call update statistics?
When would you use a before or after trigger?
What is sql server profiler?
Which is better statement or preparedstatement?
How to convert numeric expression data types using the cast() function?
Explain the disadvantages/limitation of the cursor?
How would you add a section to a table?
How data can be copied from one table to another table?
Can you move the resources after pausing the node? : sql server database administration
you have separate development and production systems you want to move a copy of a development database into production to do this, you do a backup on the development system and restore to the production system after a few minutes, you begin getting calls from several customers saying that they are denied access to the system why? : Sql server administration
What are the advantages of sql stored procedure?
Explain forward - only cursors?
How to use copy and concatenate commands in SQL?
