when use triger on View - SQL Server Forums

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

when use triger on View

Author

Topic

jooorj
Posting Yak Master

126 Posts

Posted - 2011-05-06 : 12:07:21

when use triger on View

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-06 : 12:15:53

When needed.

Jim

Everyday I learn something that somebody else already knew

jooorj
Posting Yak Master

126 Posts

Posted - 2011-05-06 : 15:05:30

i mean :how can be fired from a view if it is used as( AFTER INSERT)
how will fired from View ,as we know a View not used for INSERT DATA.
Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-06 : 15:09:42

You can certainly use a view to insert data. Triggers fire from the underlying tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-06 : 15:09:44

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

jooorj
Posting Yak Master

126 Posts

Posted - 2011-05-06 : 15:16:24

I cant understand ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-06 : 15:29:40

Which part?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jooorj
Posting Yak Master

126 Posts

Posted - 2011-05-07 : 19:00:36

.I need to know how to use triger on view and how can be fired from View,I know that view cant Insert data or updTE

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-07 : 19:18:08

quote:
I know that view cant Insert data or updTE

Well, you're wrong, as Tara has already stated, and as Books Online would tell you if you ever read it.

Try this one too: http://msdn.microsoft.com/en-us/library/ms189799.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-07 : 19:37:31

To expand on what Rob and Tara said:

: you can insert data into a view - the data of course, gets inserted into the underlying table.

: you can create a trigger on a view, but only instead of triggers.

If you can describe the problem you are trying to solve, may be someone on this forum can offer more concrete suggestions.

In any case, here is some sample code that creates a trigger on a view. It is only a sample, I would be cautious in using it anywhere it matters unless you clearly understand and test all the ramifications.

-- Create a table.
CREATE TABLE dbo.TestTable
(
	id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	val  INT NOT NULL
);
GO
-- create a view on it.
CREATE VIEW dbo.TestView AS 
SELECT
	val
FROM
	dbo.TestTable
WHERE
	val > 0;
	
GO

-- create an instead of trigger on the view. (you can only do instead of on view)
CREATE TRIGGER dbo.IWannaDoubleIt
ON dbo.TestView INSTEAD OF INSERT
AS
	INSERT INTO dbo.TestView
	SELECT
		2.0 * INSERTED.val
	FROM
		INSERTED;
	
GO
	
INSERT INTO dbo.TestTable VALUES ( 1.0 );
INSERT INTO dbo.TestView VALUES ( 33.0 );

SELECT * FROM dbo.TestTable;

DROP VIEW dbo.TestView;
DROP TABLE dbo.TestTable;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources