CODEWITHSUNDEEP
sqlsql-server-2008viewtriggerssql-update
Annoscia
Annoscia

Reputation: 117

Update Trigger SQL Server 2008

I have created a view in SQL server 2008 which consists of the joining of about 5 different tables to access the data i need.

How would i create an update trigger to update a field within this view? So for example if i wanted to update all first names to peter if the last name is smith.

If Fname = peter
update Sname to Smith
end if

Many thanks in advance

UPDATE this is what i have so far

CREATE TRIGGER SurName
   ON  ViewCustomer
   AFTER UPDATE
AS 
if FName= 'Peter'
BEGIN
    update ViewCustomer set SName= 'Smith'
    SET NOCOUNT ON;

END
GO

Upvotes: 1

Views: 10549

Answers (2)

ShyJ
ShyJ

Reputation: 4640

This should do the job.

Table and view definitions.

CREATE TABLE Customer
  (ID int,
   FName varchar(200),
   SName varchar(200),
   RoleID int);

CREATE TABLE CustomerRole
 (RID int,
  Name varchar(100));

CREATE VIEW ViewCustomer AS
SELECT *
FROM Customer JOIN CustomerRole on RoleID = RID;

Trigger definition.

CREATE TRIGGER ViewCustomerTrigger ON ViewCustomer
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE Customer 
SET FName = I.FName,
    SName = CASE I.FName WHEN 'Peter' THEN 'Smith' ELSE I.SName END,
    RoleID = I.RoleID
FROM INSERTED I JOIN Customer C ON I.ID = C.ID

UPDATE CustomerRole
SET Name = I.Name
FROM INSERTED I JOIN CustomerRole R ON I.RID = R.RID
END
GO

Sample data

INSERT INTO Customer (ID, FName, SName, RoleID)
VALUES (1, 'John', 'Wayne', 1);

INSERT INTO Customer (ID, FName, SName, RoleID)
VALUES (2, 'Jack', 'Jackson', 1);

INSERT INTO CustomerRole (RID, Name)
VALUES (1, 'Manager');

This update will cause the trigger to update the SName to 'Smith'

UPDATE ViewCustomer
SET FName = 'Peter'
WHERE ID = 1

Here is a SQLFiddle for it.

Upvotes: 3

vkamayiannis
vkamayiannis

Reputation: 735

You need to create an INSTEAD OF trigger for views. You can check on:

http://msdn.microsoft.com/en-us/library/ms188601.aspx

Upvotes: 0

Related Questions