title: Advance trigger programming logic in sql server
body: <p>This post is quite bit longer than others and I apologized for that. so if you just interest about this please help me.</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>I create a view for above tables to as below</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>the I want to write an INSTEAD OF trigger to vw_sw_manual view to do the below mentions</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>iii. else, insert rows to software_manual and cpy_software_manual tables to reflect the new information.</p>
  
  <p>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 ?</p>
  
  <pre><code>    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
  </code></pre>