TSQL - Instead of Update Trigger for a view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TSQL - Instead of Update Trigger for a view

Author

Topic

emman
Starting Member

2 Posts

Posted - 2012-03-19 : 22:36:17

Hi, I need to update data from a View which contains columns from multiple tables, for example the table has an id number for a field say locationID which is linked to a table with locationID and locationName.

I have created an update trigger see below. Is it possible to refer to the Update records using the UPDATE AS U?

USE [TESTDB];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER [dbo].[trg_UpdateRegister] ON [dbo].[view_Register]
WITH EXEC AS CALLER
INSTEAD OF UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here
IF (EXISTS (select E.PersonNumber FROM Employee_Assignment AS E, INSERTED as U WHERE E.PersonNumber = U.PersonNumber))

UPDATE E.Employee_Assignment

SET SubmittalDate = U.SubmittalDate,
ProposedStartDate = U.ProposedStartDate,
ProposedEndDate = U.ProposedEndDate,
SubmittedBy = U.SubmittedBy

FROM INSERTED U WHERE E.assignment_id = U.assignment_id

END

Please help, I dont know if I am trying to do the impossible.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2012-03-20 : 00:05:07

Try this way out..

UPDATE E

SET SubmittalDate = U.SubmittalDate,
ProposedStartDate = U.ProposedStartDate,
ProposedEndDate = U.ProposedEndDate,
SubmittedBy = U.SubmittedBy

FROM Employee_Assignment E JOIN INSERTED U ON E.assignment_id = U.assignment_id

------------------------------------------------
The answer is always no till than you don't ask.

emman
Starting Member

2 Posts

Posted - 2012-03-20 : 22:50:11

Thanks very much, this is working now using your suggestion. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources