Trigger - HOw to copy data from view to table

[Lava]

Hi -

We have a need to copy data from a view to a table whenever the view gets updated. Can you please help me here on how to write a trigger to insert data from view whenever data gets modified?

Thanks.

[rmiao]

Is table behind view? When you update view, it's update tables the view based on.

[Lava]

The view data is coming from other views which their views are coming from other tables! This is the way this view is set up, don't ask me why!
So basically, the data of the view is coming from other views which is how its value get updated. So I need to write a triger to insert the value of that view into a table whenever there is a data modification in the view.

[ajcalderon3]

First a few questions:
What RDBMS are you using?
and is the view updateable?
and is the data being updated view the view: i.e.

update NameOfView
set col1 = 'this'
?

In SQL Server 2000 and 2005
you can use an instead of trigger

here is an example from
http://www.sql-server-performance.com/nn_triggers.asp

Create trigger trgOnView

on vwUserMaster

INSTEAD OF INSERT

AS

begin

Print ('INSTEAD OF Trigger [trgOnView] – vwUserMaster !!!')

End



INSERT INTO VWUSERMASTER(USERNAME, PASSWORD)

VALUES('Damerla','Venkat')



Output

INSTEAD OF Trigger [trgOnView] – vwUserMaster !!

(1 row(s) affected)

So whenever a user tries to insert data into the view vwUserMaster, the INSTEAD OF trigger trgOnView will automatically be executed.

In SQL SERVER 2000, views can be used to INSERT/DELETE and UPDATE the data in the multiple tables, and this can be achieved using INSTEAD OF triggers.



CREATE VIEW vwUser

AS

SELECT

[User_Master].[Username],

[User_Master].[Password],

[User_Details].[FName],

[User_Details].[MName],

[User_Details].[LName],

[User_Details].[Email]

FROM

[User_Master], [User_Details]

WHERE

[User_Master].[UserID]=[User_Details].[UserID]



CREATE TRIGGER tgrInsertData

ON vwUser

INSTEAD OF INSERT

AS

BEGIN

Declare @UserName varchar(50)

Declare @Password varchar(50)

Declare @FName varchar(50)

Declare @MName varchar(50)

Declare @LName varchar(50)

Declare @Email varchar(50)



SELECT

@UserName = UserName,

@Password = Password,

@FName = FName,

@MName = MName,

@LName = LName,

@Email = Email

FROM INSERTED



INSERT INTO User_Master(UserName, Password)

VALUES(@UserName, @Password)



INSERT INTO User_Details(UserID,FName,LName,MName,Email) VALUES(@@Identity, @FName, @LName, @MName, @Email)



END



INSERT INTO vwUser(UserName,Password,FName,LName,MName,Email)

VALUES ('Dhananjay','Dhananjay','Dhananjay','Nagesh',NULL ,

'Dhananjay@kdmnn.com'



Output

AFTER Trigger [trgInsert] – Trigger executed !!

AFTER Trigger [trgInsert2] – Trigger executed !!

AFTER Trigger [trgInsert3] – Trigger executed !!

AFTER Trigger [trgEncrypted] Encrypted – Trigger executed !!

(1 row(s) affected)

AFTER Trigger [trgAfterInsert] – Trigger executed !!

(1 row(s) affected)

Then check the data in the following tables User_Master and User_Details. The new row gets inserted in both the tables.

A view or table can have only one INSTEAD OF trigger for each INSERT, UPDATE and DELETE events.

We have seen that you can create any number of AFTER triggers on the table for the same event, whereas you cannot do that with INSTEAD OF triggers.



CREATE TRIGGER trgInsteadOfTrigger1

ON vwUserMaster

INSTEAD OF UPDATE

AS

BEGIN

Print ('INSTEAD OF Trigger [trgInsteadOfTrigger1] – Trigger executed !!')

END



CREATE TRIGGER trgInsteadOfTrigger2

ON vwUserMaster

INSTEAD OF UPDATE

AS

BEGIN

Print ('INSTEAD OF Trigger [trgInsteadOfTrigger2] – Trigger executed !!')

END



Output

Server: Msg 2111, Level 16, State 1, Procedure trgInsteadOfTrigger2, Line 6

Cannot CREATE trigger 'trgInsteadOfTrigger2' for view 'vwUserMaster' because an INSTEAD OF UPDATE trigger already exists.

From the output, it is clear that you cannot create two INSTEAD OF triggers on the view/ table for the same event.

Note: An important point to be noted is that INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined.

At last, how would you know what are the triggers associated with the table and what type of the trigger it is? Whether AFTER or INSTEAD OF?

The solution for this question is sp_helptrigger. This stored procedure gives all the information about the triggers such as Event on which the trigger gets executed, the type of the trigger, etc.



Sp_helptrigger User_Master



Output

trigger_name trigger_owner isupdate isdelete isinsert isafter isinsteadof

trgInsert dbo 0 0 1 1 0

trgInsert2 dbo 0 0 1 1 0

trgInsert3 dbo 0 0 1 1 0

trgEncrypted dbo 0 0 1 1 0



Triggers can be used in the following scenarios, such as if the database is de-normalized and requires an automated way to update redundant data contained in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical value in another table.

Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce the data integrity using CONSTRAINTS, RULES and DEFAULTS. Triggers cannot be created on the temporary tables.



Navneeth Diwaker Naik (navneeth_naik@satyam.com) is an employee of Satyam Computers Services Limited, Hyderabad, Andhra Pradesh. [INDIA].

Published with the explicit written permission of the author. Copyright 2004.