SQLTeam.com | Weblogs | Forums

INSTEAD OF INSERT Triggers

sql2008r2

#1

Hello,

I am having a problem with Triggers using a view. When I use INSERT INTO to a table, the table will return zero record (suppose to return 4 records). I would appreciate if you could help me on this matter.
Here is the query.

USE MY_DB

GO
CREATE TABLE Levels (It nvarchar(20), Lev nvarchar(20))
GO

Create View Step1 AS
SELECT top 4 ITT1.Father AS It, '1' AS Lev
FROM OITM INNER JOIN ITT1 ON OITM.ItemCode = ITT1.Father
GO

CREATE TRIGGER Trig_INS on Step1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [Levels]
SELECT It, Lev FROM inserted As MyTable
END
GO

select * from Step1
GO
select * from [Levels]
GO

The table "Levels" returns 0 record instead of 4 records.


#2

Hi,
How do you perform the insert? You missed to show that code.


#3
CREATE TRIGGER Trig_INS on Step1
INSTEAD OF INSERT
AS
 BEGIN
 INSERT INTO [Levels]                           --- Here is the code
 SELECT It, Lev FROM inserted As MyTable
END
GO

#4

I think dennisc meant the code which inserts into the [Step1] VIEW - i.e. that then causes taht trigger to fire.

If you think that this is it:

select * from Step1
GO

then that will only select data from the [Step1] VIEW, you need to INSERT INTO [Step1] in order to fire the trigger (and then you should have some new data in [Levels] :smile: )


#5

If I use INSERT INTO [Step1] from the [Step1] view, still do not see how to add records to [Levels] table which has empty records. The [Step1] already has records and I need to use INSERT INTO so be able to add records from [Step1] to [Levels] table.


#6

Sorry, still not understanding how you are expecting this to work.

Your INSERT trigger on [Step1]

CREATE TRIGGER Trig_INS on Step1
INSTEAD OF INSERT
...

only "fires" when you INSERT a record into [Step1]

e.g.

INSERT INTO [Step1]
(
	[It], [Lev]
)
SELECT	N'ABC', N'XYZ'
--
SELECT *
FROM [Levels]

If you want data to be inserted into [Levels] when records are inserted into the underlying tables for the view [Step1] - i.e. inserted into OITM and ITT1 then this is not possible. (You would need to put a trigger on OITM and/or ITT1

Sorry if I am misunderstanding your requirement, perhaps you could clarify?


#7

I am trying to update “Levels” table. Each time when the query is run (1- delete Levels table, 2-create Levels table ) , then “Levels” table get fresh data from Table1 and Table2 by using INSERT INTO (In my sample uses INTO). And I would like to use combination of INSERT INTO and view. Here is my sample.

if exists (select * from information_schema.tables where table_name = 'levels') drop table levels
go
create table levels (it nvarchar(20), lev nvarchar(20))
go
if object_id('step1', 'v') is not null drop view step1 go
create view step1 as select a,b,c from table1 go
select * into levels from step1 – 1
if object_id('step2', 'v') is not null drop view step2 go
create view step2 as select a,b,c from table2 go
select * into levels from step2 – 2

I hope this clarifies my question


#8

Hi billj,
I hope I understanding what you are asking (correct me if I'm wrong), you want to re-populate data after you re-create table "levels". But looking at your code, it wont work, as you looking to insert to levels table which have 2 columns from a view that have 3. I would just assume you got a typo, if that is the case, then it will work.

But all those code, you do not require a insert of trigger, instead of trigger fires when you try to inset to a given object, so instead doing a simple insert, it will do the code you define. If you only want to re-populate the data after you re-create a table, you do not require a instead of trigger.

Hope this helps.


#9

I wonder if the O/P wants:

Create [Levels] table
Create [Step1] view - use it to query data into [Levels]
Create [Step2] view - use it to query data into [Levels]

THEN create an Insert trigger on the view(s) so any NEW data can be inserted that way?

Sorry, I'm still having trouble understanding the requirement.


#10

hmm, but he is dropping the view and re-creating them based on table1, and then performed a select into table levels from the newly created view. I'm confused as of why a instead of trigger is required given its a simple re-populate of data.

Maybe I misunderstand the question totally :smile:


#11

Indeed. I think we just need some help to understand the requirement properly, and then we can help with the answer :smile: