/*********************
  WORKING WITH TRIGGERS
**********************/

--Look at our item table
select * from Item

drop table ItemAudit

--Create a Table to hold the Item audit
CREATE TABLE [dbo].[ItemAudit](
	[ItemNum] [char](4) NOT NULL,
	[Description] [char](15) NULL,
	[onhand] [decimal](4, 0) NULL,
	[Category] [char](2) NULL,
	[Storehouse] [char](1) NULL,
	[Price] [decimal](6, 2) NULL,
	[dateTimeCreated] smalldatetime NULL,
	[Operation] varchar(50)
)
--Add the username column to the table
alter table ItemAudit
add UserName varchar(50)
GO

--If the trigger exists, drop it first
drop trigger trg_InsertItemAudit
GO

/*********************
  Creating a Trigger
**********************/

--Triggers are attached to a table and react o either an insert, delete or 
--update on the table.

--Here is the SQL code to create the trigger
--which will insert a record into our audit table
--if either the operation is an insert or a delete
create TRIGGER trg_InsertItemAudit
ON Item -- < This is the table that the trigger is attached to
After -- < This is when the trigger will be execute (After the operation on the table)
Insert,Delete, update --< These are areh operations that the trigger supports

AS
Begin
     --If statement checks to see if there were any records inserted
	 if exists(select 1 from inserted) 
	 -- < this "inserted" recordset holds the records which were inserted in the original table.
	 BEGIN 
		 INSERT INTO ItemAudit 
		 (Itemnum, description,onhand,category,storehouse,
		 price,datetimecreated,Operation,username )
		 SELECT Itemnum, description, onhand, category, 
		 storehouse, price, getdate(), 'INSERT',System_user
		 FROM Inserted

		 --This will insert into the itemaudit those records inserted into the original table
	 END
	   --If statement checks to see if there were any records deleted
	 if exists(select 1 from deleted)
	 -- < this "deleted" recordset holds the records which were delete in the original table. 
	 begin
		INSERT INTO ItemAudit 
		 (Itemnum, description,onhand,category,storehouse,
		 price,datetimecreated,Operation,username )
		 SELECT Itemnum, description, onhand, category, 
		 storehouse, price, getdate(), 'DELETE',SYSTEM_USER
		 FROM deleted

		 --This will insert into the itemaudit those records deleted into the original table
	 end

	 --Update keyword is used to determin if there was an update
	 if update(description)
		print 'Updated item ' 
End
GO


--This will insert a record into the Item table, which will
--cause the trigger to be executed since its triggering on inserts
insert into Item
values
('SM02','Electric Stove SM02',49,'AP',3,300.00)

select * from item

select * from ItemAudit

--This update statement will also cause the trigger to execute since
--it is listening to update statements
update Item
set Description = 'Electric Stove4'
where itemnum = 'SM02'

--Thhis delete statement will also cause the trigger to execute since
--it is listening to the delete statements
delete from Item where ItemNum = 'SM02'

--Review all the records that got inserted into our audit table
select * from ItemAudit







