1

Topic: Problem with trigger Instead Of Insert

Good afternoon!
There is a representation which affects two databases:

Create view AssortVse with view_metadata as
SELECT
[Globas-PoizvodstvoSQL].dbo.. ,
[Globas-PoizvodstvoSQL].dbo.. ,
[Globas-PoizvodstvoSQL].dbo.. It is old,
[SQL].dbo.. The price,
[Globas-PoizvodstvoSQL].dbo.. Assignment,
[Globas-PoizvodstvoSQL].dbo.. APPLICABILITY
FROM [Globas-PoizvodstvoSQL].dbo. LEFT JOIN [SQL].dbo. ON [Globas-PoizvodstvoSQL].dbo..  = [SQL].dbo.. 

For the given representation I write the following trigger:

USE [SQL]
GO
/ ****** Object: Trigger [dbo]. [InsteadInsertTriggerAssortimentPoln1] Script Date: 2/12/2018 OF 10:48:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo]. [InsteadInsertTriggerAssortVse] on [dbo]. []
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [Globas-PoizvodstvoSQL].dbo. (, It is old, Assignment, APPLICABILITY)
SELECT Naimen, Is old, Assignment, APPLICABILITY
from inserted
Insert into [SQL].dbo. (KodIzdel, the Price)
Select KodIzdel, the Price
from inserted
END;

The problem in that for the table [Globas-poizvodstvoSQL].dbo. the field of KodIzdel is a unique identifier, therefore system appropriates it automatically. And for the table [SQL].dbo. It is simple a field and if it not to set - is filled null.
I can not understand how correctly to add the trigger, what as the field of KodIzdel for the table of AssortKonf was filled.
In advance thanks

2

Re: Problem with trigger Instead Of Insert

volt;
Somehow so

DECLARE @x1 TABLE (Id int, code VARCHAR (1))
INSERT INTO dbo.x1 (Code)
OUTPUT
inserted. Id;
inserted. Code
INTO @x1
SELECT Code
FROM inserted
INSERT INTO dbo.x2
SELECT Id
FROM @x1

3

Re: Problem with trigger Instead Of Insert

TaPaK;
Wrote the trigger,

ALTER TRIGGER [dbo]. [InsteadInsertTriggerAssortVse2] on [dbo]. []
INSTEAD OF INSERT
AS
BEGIN
Declare @t table (KodIzdel int, the price money)
INSERT INTO [Globas-PoizvodstvoSQL].dbo. (, It is old, Assignment, APPLICABILITY)
output inserted., inserted. into @t
SELECT Naimen, Is old, Assignment, APPLICABILITY
from inserted;
Insert into [SQL].dbo. (KodIzdel, the Price)
Select KodIzdel, the price from @t
END;

Produces an error - invalid column name ' the price '. I so understand, because in the table the Assortment misses parameter the price.

4

Re: Problem with trigger Instead Of Insert

TaPaK;
Corrected the following trigger:

ALTER TRIGGER [dbo]. [InsteadInsertTriggerAssortVse2] on [dbo]. []
INSTEAD OF INSERT
AS
BEGIN
Declare @t table (KodIzdel int)
INSERT INTO [Globas-PoizvodstvoSQL].dbo. (, It is old, Assignment, APPLICABILITY)
output inserted. into @t
SELECT Naimen, Is old, Assignment, APPLICABILITY
from inserted;
Insert into [SQL].dbo. (KodIzdel, the Price)
Select qq., the price from @t as qq, inserted
Select qq., the price from @t as qq, inserted
END;

Like works, it is necessary to check up only that will if add one request some lines

5

Re: Problem with trigger Instead Of Insert

volt;
CROSS JOIN wonderfully shades the general cadaveric flavor of this horror easy notes of a clotted blood from the raked foot.

6

Re: Problem with trigger Instead Of Insert

Corrected the following ;
I already found out that it is wrong...

7

Re: Problem with trigger Instead Of Insert

volt;
Well to connect yours the price with , moreover and from representation, I can invent only a variant with MERGE instead of insert
And yes, I consider this all as the full perversion smile

8

Re: Problem with trigger Instead Of Insert

TaPaK;
MERGE quite to itself a method.
And here the full perversion is to create for Assortment representation with  a column the price and the trigger instead of insert.

9

Re: Problem with trigger Instead Of Insert

invm;
Unfortunately, the problem is deeper... Is  written on Access. IT should be translated on SQL the most simple method. Therefore especially to climb in logic of a DB there is no desire, and it is necessary to build crutches.

10

Re: Problem with trigger Instead Of Insert

volt wrote:

Is  written on Access. It is necessary to translate on SQL

to translate for the sake of to translate or that then worked normally?