CODEWITHSUNDEEP
sqlsql-servert-sql
KrzK
KrzK

Reputation: 35

Trigger instead of insert on view

There are two tables(info, info2) and I have join them toghether and create a view (v_fullinfo) for them. I have also created a trigger(t_1) on that view which job is to insert inserted data to another table (tabinfo) on another db. I have read that "after insert" does not work on views and I should use "Instead of instert". Unfortunetaly when I insert data into tables "info" and "info2" It does not appear in "tabinfo". How to dinamicaly insert data from that view to another table?

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[info](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[lastname] [nchar](10) NULL,
[gender] [nchar](10) NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[info2](
[id] [int] IDENTITY(1,1) NOT NULL,
[country] [nchar](10) NULL,
[city] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[info] ([id], [name], [lastname], [gender]) VALUES (1, 
N'Johnny', N'English   ', N'Male      ')

INSERT [dbo].[info] ([id], [name], [lastname], [gender]) VALUES (2, N'John'
, N'Doe       ', N'Male      ')

INSERT [dbo].[info] ([id], [name], [lastname], [gender]) VALUES (3, 
 N'Susan',   N'Stewart   ', N'Female    ')

 INSERT [dbo].[info] ([id], [name], [lastname], [gender]) VALUES (4, 
  N'Frank', N'Moore     ', N'Male      ')

INSERT [dbo].[info] ([id], [name], [lastname], [gender]) VALUES (5, N'Jan'
 , N'Kowalski  ', N'Male      ')


 INSERT [dbo].[info2] ([id], [country], [city]) VALUES (1, N'USA       ', 
 N'New York  ')

  INSERT [dbo].[info2] ([id], [country], [city]) VALUES (2, N'USA       ', 
  N'Washington')

 INSERT [dbo].[info2] ([id], [country], [city]) VALUES (3, N'UK        ', 
 N'London    ')

 INSERT [dbo].[info2] ([id], [country], [city]) VALUES (4, N'Irland    ', 
 N'Dublin    ')    

 CREATE view [dbo].[v_fullinfo]
 as
   SELECT dbo.info.id, dbo.info.name, dbo.info.lastname, 
 dbo.info.gender, dbo.info2.country, dbo.info2.city
  FROM            dbo.info LEFT JOIN
                     dbo.info2 ON dbo.info.id = dbo.info2.id


 create TRIGGER [dbo].[t_1] 
  ON  [dbo].[v_fullinfo] 
  INSTEAD OF insert

   AS 

  BEGIN
SET NOCOUNT ON;
 if (select count(*) from inserted )>1
 begin
      raiserror ('nie mozesz',16,1)
    rollback
 end

 declare @id int
declare @name nchar(10)
declare @lastname nchar(10)
declare @gender nchar(10)
declare @country nchar(10)
declare @city nchar(10)

  select
     @id=id, 
    @name=name,
    @lastname=lastname,
    @gender=gender,
    @country=country,
    @city=city
    from inserted
    insert [test2].[dbo].[tabinfo] (id, name, lastname,gender, country, 
   city)
    values (@id, @name, @lastname,@gender, @country, @city)
   end

  `CREATE TABLE [dbo].[tabinfo](
    [id] [int] NULL,
   [name] [nchar](10) NULL,
   [lastname] [nchar](10) NULL,
   [gender] [nchar](10) NULL,
    [country] [nchar](10) NULL,
   [city] [nchar](10) NULL
   ) ON [PRIMARY]

`

Upvotes: 0

Views: 231

Answers (1)

SQLBadPanda
SQLBadPanda

Reputation: 635

Your trigger is on the view but you are inserting to the underlying tables, thus your trigger is not being triggered.

Either put an AFTER UPDATE trigger on each table or change your INSERTs to insert to the view.

Note that your INSTEAD OF trigger happens instead of the intended DML action, i.e. your intended insert will not happen, you will have to handle that yourself. For example, with an INSTEAD OF on a view, when you insert to a view you will need to take those values and perform whatever inserts on underlying tables that you need to do.

Upvotes: 2

Related Questions