-
Trigger - HOw to copy data from view to table
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.
-
Is table behind view? When you update view, it's update tables the view based on.
-
How to copy data from view to table
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.
-
Trigger on a view
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|