Advance trigger programming logic in sql server

Question

This post is quite bit longer than others and I apologized for that. so if you just interest about this please help me.

CREATE TABLE software_manual(
item_no varchar(50),
title varchar(50) NOT NULL,
description varchar(500),
manufacturer varchar(50),
version varchar(50),

CONSTRAINT software_manual_pk PRIMARY KEY(item_no),
CONSTRAINT check_sw_item_no CHECK (title LIKE '[S]%')
);

CREATE TABLE cpy_item(
access_no int,
status varchar(50),

CONSTRAINT cpy_item_pk PRIMARY KEY(access_no),
CONSTRAINT check_status CHECK(status IN ('Borrowed','Lost','Stolen','Available'))
);

CREATE TABLE cpy_software_manual(
access_no int,
item_no varchar(50),

CONSTRAINT cpy_software_manual_pk PRIMARY KEY(access_no),
CONSTRAINT cpy_software_manual_fk FOREIGN KEY(item_no) REFERENCES software_manual(item_no),
CONSTRAINT cpy_software_manual_fk2 FOREIGN KEY(access_no) REFERENCES cpy_item(access_no)
);

I create a view for above tables to as below

CREATE VIEW vw_sw_manual (title,manufacturer,version,access_no)
AS
SELECT sm.title,sm.manufacturer,sm.version,csm.access_no
FROM software_manual sm, cpy_software_manual csm
WHERE sm.item_no=csm.item_no;

the I want to write an INSTEAD OF trigger to vw_sw_manual view to do the below mentions

i. if the inserted row contains an access no that exists in the table, then update the version, title and manufacturer columns of software_manual table with the inserted data.

ii. else, if the title, manufacturer and version exists in the sofware_manual table, insert a new row to cpy_software_manual table with its item information referencing the existing row of the software_manual table

iii. else, insert rows to software_manual and cpy_software_manual tables to reflect the new information.

I wrote a trigger as below, and it contains some logical errors. in part ii. of my question @item_no gets null values in my trigger. can anyone helps me to figure out this ?

    alter TRIGGER tr_sw_vw_insert
    ON vw_sw_manual INSTEAD OF INSERT
    AS
    BEGIN
    DECLARE @insertAccessNo int
    DECLARE @title varchar(50)
    DECLARE @manufacturer varchar(5)
    DECLARE @version varchar(50)

    DECLARE @itemNo varchar(50)

    DECLARE @maxAccessNo int
    SELECT @maxAccessNo=MAX(access_no) FROM cpy_software_manual;

    SELECT @insertAccessNo=access_no,@title=title,@manufacturer=manufacturer,@version=version FROM inserted;

    IF(@insertAccessNo IS NOT NULL)
     BEGIN -- if @insertAccessNo IS NOT NULL do this
        SELECT @itemNo=item_no FROM cpy_software_manual WHERE access_no=@insertAccessNo;
        UPDATE software_manual SET title=@title,version=@version,manufacturer=@manufacturer
            WHERE item_no=@insertAccessNo;
     END
    ELSE IF(@title IS NOT NULL AND @manufacturer IS NOT NULL AND @version IS NOT NULL)
      BEGIN     

        INSERT INTO cpy_item(access_no,status) VALUES(@maxAccessNo+1,'Available');
        SELECT @itemNo=item_no FROM software_manual
          WHERE title=@title AND manufacturer=@manufacturer AND version=@version;

        INSERT INTO cpy_software_manual(access_no,item_no) VALUES(@maxAccessNo+1,@itemNo);

      END
    ELSE
      BEGIN-- else do this 
            DECLARE @maxItemNo int
            SELECT @maxItemNo=MAX(item_no) FROM software_manual;

            INSERT INTO software_manual(item_no,title,version,manufacturer)
                VALUES(@maxItemNo+1,@title,@version,@manufacturer);
            INSERT INTO cpy_item(access_no,status) VALUES(@maxAccessNo+1,'Available');
            INSERT INTO cpy_software_manual(access_no,item_no) VALUES(@maxAccessNo+1,@maxItemNo+1);
      END
END

Are you adding the rows during the insert that you want to refer to? If so you need to refer to the `INSERTED` table instead of the actual data table which at this point does not have the data yet. — JNK, Mar 07 '14 at 20:11
ANd triggers need to be written with the assumption that more than one record will be inserted/updated or deleted, so you should not be setting anything to a scalar variable. — HLGEM, Mar 07 '14 at 20:22
when insert the data to view, trigger should update/insert the data according to the logic. In this situation (i.e question ii.) INSERTED table hasn't item_no column(primary key) that's why I got the data in actual table — Roledenez, Mar 07 '14 at 20:27
@HLGEM I'm very new to DB programming, can you suggest something ? — Roledenez, Mar 07 '14 at 20:30
Cannot you put that logic in the app BL layer? waiting for "cannot change app in 3,2,1..." — jean, Mar 07 '14 at 20:31
@jean For learning purpose I want to write a T-SQL query for it. — Roledenez, Mar 07 '14 at 20:35
Ok for learning purposes. I learned the hard way putting BL logic inside the DB can got you maintenance problems. triggers also just turns things worse (for maintenance). Hope you got better luck =) — jean, Mar 07 '14 at 20:38
Triggers really are a last resort. You're much better off creating a stored proc that accepts the data you want to insert, and works all of this out for you. — Nick.Mc, Jan 22 '19 at 13:08

score 0 · Answer 1 · edited Jan 22 '19 at 12:28

alter TRIGGER tr_sw_vw_insert
ON vw_sw_manual INSTEAD OF INSERT
AS
BEGIN
DECLARE @insertAccessNo int
DECLARE @title varchar(50)
DECLARE @manufacturer varchar(5)
DECLARE @version varchar(50)

DECLARE @itemNo varchar(50)

DECLARE @maxAccessNo int
SELECT @maxAccessNo=MAX(access_no) FROM cpy_software_manual;

SELECT @insertAccessNo=access_no,@title=title,@manufacturer=manufacturer,@version=version FROM inserted;

IF(@insertAccessNo IS NOT NULL)
 BEGIN -- if @insertAccessNo IS NOT NULL do this
    SELECT @itemNo=item_no FROM cpy_software_manual WHERE access_no=@insertAccessNo;
    UPDATE software_manual SET title=@title,version=@version,manufacturer=@manufacturer
        WHERE item_no=@insertAccessNo;
 END
ELSE IF(@title IS NOT NULL AND @manufacturer IS NOT NULL AND @version IS NOT NULL)
  BEGIN     

    INSERT INTO cpy_item(access_no,status) VALUES(@maxAccessNo+1,'Available');
    SELECT @itemNo=item_no FROM software_manual
      WHERE title=@title AND manufacturer=@manufacturer AND version=@version;

    INSERT INTO cpy_software_manual(access_no,item_no) VALUES(@maxAccessNo+1,@itemNo);

  END
ELSE
  BEGIN-- else do this 
        DECLARE @maxItemNo int
        SELECT @maxItemNo=MAX(item_no) FROM software_manual;

        INSERT INTO software_manual(item_no,title,version,manufacturer)
            VALUES(@maxItemNo+1,@title,@version,@manufacturer);
        INSERT INTO cpy_item(access_no,status) VALUES(@maxAccessNo+1,'Available');
        INSERT INTO cpy_software_manual(access_no,item_no) VALUES(@maxAccessNo+1,@maxItemNo+1);
  END
END

Advance trigger programming logic in sql server

1 Answers1