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.
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] )
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.
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?
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
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.
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.
)