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

What are the authentication modes in sql server? How can it be changed?

812

---

What options are available to audit login activity? : sql server security

843

---

Is t sql the same as sql server?

751

---

Which table keeps information about stored procedures?

728

---

What are partitioned views?

754

---

What is a constant or literal in ms sql server?

802

---

What is the difference between varchar and varchar types?

760

---

Explain Reporting Life Cycle?

102

---

What is delete query?

713

---

What are security principals used in sql server 2005?

686

---

What are the steps you should follow to start sql server in single-user mode?

683

---

You are designing a database for your human resources department in the employee table, there is a field for social security number, which cannot contain null values if no value is given, you want a value of unknown to be inserted in this field what is the best approach?

857

---

What is unique key constraint?

823

---

Explain timestamp datatype?

725

---

can a database be shrunk with users active? : Sql server administration

776

---