UnpNPAsz

1create table dude (
2    id int identity not null primary key,
3    name sysname unique
4);
5go
6create table car (
7    id int identity not null primary key,
8    name sysname,
9    dude_id int not null foreign key references dbo.dude(id)
10);
11go
12create view DudeCar 
13as
14    select 
15        dude_id = d.id, 
16        dude_name = d.name, 
17        car_id = c.id, 
18        car_name = c.name
19    from dbo.dude d
20    join dbo.car c on c.dude_id = d.id
21go
22create trigger trDudeCar
23on dbo.DudeCar
24instead of insert
25as 
26begin;
27    insert dbo.dude (name)
28    select 
29        dude_name
30    from Inserted;
31
32    insert dbo.car (name, dude_id)
33    select distinct
34        i.car_name,
35        d.id
36    from Inserted i
37    join dbo.dude d on d.name = i.dude_name;
38end;
39go
40insert dbo.dude (name)
41values ('peter');
42select * from dbo.dude d
43
44insert dbo.car (name, dude_id)
45values ('toyota',1 );
46
47select * from dbo.DudeCar dc
48
49insert dbo.DudeCar (dude_name, car_name)
50values ('kate', 'nissan' );
51
52drop trigger if exists trDudeCar;
53drop table if exists car;
54drop table if exists dude;
55drop view if exists dbo.DudeCar;