CODEWITHSUNDEEP
sqlsql-servert-sql
JBarberU
JBarberU

Reputation: 1029

Create trigger on dummy view

I'm having problem creating a trigger. The database model look something like this:

CREATE TABLE Unit (
SerialNO    VARCHAR(3)
PRIMARY KEY (serialNO)
);

CREATE TABLE PackageConfig (
PartNO      VARCHAR(15),
SomeValue   VARCHAR(20),
PRIMARY KEY (PartNO)
);

CREATE TABLE Package (
ID          INT IDENTITY(1,1),
Config      VARCHAR(15),
PRIMARY KEY (ID),
FOREIGN KEY (Config) REFERENCES PackageConfig(PartNO)
);

CREATE TABLE UnitInPackage (
Package     INT,
Unit        VARCHAR(3),
PRIMARY KEY (Package,Unit),
FOREIGN KEY (Package) REFERENCES Package(ID),
FOREIGN KEY (Unit) REFERENCES Unit(SerialNO)
);

There are Units, PackageConfigurations and Packages. A package has exactly one PackageConfiguration and the relation between Package and Unit is a many (units) to at most one (package).

What I'm trying to accomplish is a trigger on a view that would let me do the following (assuming that the PackageConfig and Units exist):

INSERT INTO v_MultiUnitPackage (PackageConfig, Unit1, Unit2, Unit3) 
VALUES ('SomeConfig','abc','bcd','cde');

Which inside the trigger would do something like:

DECLARE @last INT;

INSERT INTO Package (Config) VALUES ('SomeConfig');
SET @last = SCOPE_IDENTITY();
INSERT INTO UnitInPackage (Package,Unit)
VALUES (@last,'abc'),(@last,'bcd'),(@last,'cde');

Or for more columns:

INSERT INTO Package (Config) VALUES ('SomeConfig');
SET @last = SCOPE_IDENTITY();
INSERT INTO UnitInPackage (Package,Unit)
VALUES (@last,'hgf'),(@last,'gfe'),(@last,'fed'),(@last,'edc'),(@last,'dcb'),(@last,'cba');

To summarize, I'd like to create a trigger that takes a PackageConfig and N-number of Units and insert them in the corresponding tables.

I've been looking into creating a dummy view that simply has the correct data types and a great enough number of columns to allow for the number of Units I want but didn't find a solution.

I also looked into something like grouping by Package.ID and for each group selecting the first unit that hasn't already been selected to a previous column. Since GROUP BY is only usable with aggregate functions I'm unsure how to realize this idea.

Realistically I don't see ever needing more than 5 or so Units but would prefer a generic solution to my problem.

Perhaps there is a really simple solution that I'm just not seeing. Any help is greatly appreciated.

Upvotes: 0

Views: 761

Answers (1)

nozari
nozari

Reputation: 504

Insert with a view is one way of doing it, so you just need to have your dummy view update with the number of units you need. Here is how the trigger should looks like:

create  view v_MultiUnitPackage
as
select 'Some-Config' as PackageConfig, 'abc' as Unit1, 'bcd' as Unit2, 'cde' as Unit3
go

  create TRIGGER tg_I_v_MultiUnitPackage
ON v_MultiUnitPackage 
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @last INT;

    INSERT INTO Package (Config)
    SELECT inserted.PackageConfig
    FROM inserted
    SET @last = SCOPE_IDENTITY();

    INSERT INTO UnitInPackage (Package,Unit)
    SELECT @last, inserted.Unit1
    FROM inserted;

    INSERT INTO UnitInPackage (Package,Unit)
    SELECT @last, inserted.Unit2
    FROM inserted;

    INSERT INTO UnitInPackage (Package,Unit)
    SELECT @last, inserted.Unit3
    FROM inserted;
END

A better option in my opinion, would be passing the units to a stored procedure as XML and handle the insert there. Here is how the procedured should looks like:

CREATE PROCEDURE usp_i_PackageUnits
(
    @PackageConfig varchar(15),
    @Units xml -- <root><unit>abc</unit><unit>bcd</unit><unit>cde</unit>
)
AS
BEGIN
    DECLARE @last INT;
    INSERT INTO Package (Config) VALUES (@PackageConfig);
    SET @last = SCOPE_IDENTITY();

    INSERT INTO UnitInPackage (Package,Unit)
    select node.value('(.)[1]', 'VARCHAR(3)') from @xml.nodes('/root/unit')as result(node)

END

Upvotes: 1

Related Questions