Question

Write a trigger to record any insertion changes in the stock table. Be sure to create a table to record any changes. Submit all your SQL code as well a screenshot of the new table showing any new insertion entries.

Answer 1

Hi,

Please find the Table and Trigger created in SQL: Please make sure the below tables are not exists in your database

--DROP TABLE IF EXISTS dbo.Results;
--DROP TABLE IF EXISTS dbo.ThereIsATriggerOnThisTable;
 
CREATE TABLE dbo.Results (Results VARCHAR(4000));

CREATE TABLE dbo.ThereIsATriggerOnThisTable (
  col1 VARCHAR(100) PRIMARY KEY
, col2 VARCHAR(100) NOT NULL DEFAULT(NEWID())
, col3 VARCHAR(100) NULL);
GO

CREATE TRIGGER dbo.MyTrigger ON dbo.ThereIsATriggerOnThisTable AFTER INSERT,UPDATE,DELETE AS
BEGIN
  DECLARE @Message VARCHAR(4000);
  SET @Message = 'I am proof the trigger did execute for ' + CAST(@@ROWCOUNT AS VARCHAR(4)) + ' row(s).'
  INSERT INTO dbo.Results VALUES (@Message);
 
  IF UPDATE(col1) INSERT INTO dbo.Results VALUES ('UPDATED says I updated col1');
  IF UPDATE(col2) INSERT INTO dbo.Results VALUES ('UPDATED says I updated col2');  
  IF UPDATE(col3) INSERT INTO dbo.Results VALUES ('UPDATED says I updated col3');
 
       IF COLUMNS_UPDATED() & 7 = 7 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1, col2, AND col3');  
  ELSE IF COLUMNS_UPDATED() & 6 = 6 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col2 AND col3');
  ELSE IF COLUMNS_UPDATED() & 5 = 5 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1 AND col3');
  ELSE IF COLUMNS_UPDATED() & 4 = 4 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col3');
  ELSE IF COLUMNS_UPDATED() & 3 = 3 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1 AND col2');  
  ELSE IF COLUMNS_UPDATED() & 2 = 2 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col2');  
  ELSE IF COLUMNS_UPDATED() & 1 = 1 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1');
 
END;

once you execute the above SQL then

Let's observe the Insert Trigger:

--Test SQL Server Trigger for INSERT

-- clear out table
TRUNCATE TABLE dbo.Results;

-- issue an insert command
INSERT INTO dbo.ThereIsATriggerOnThisTable(col1) VALUES ('BEFORE');

-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

Here you can see that if we insert any record in the stock table then Insert trigger created new entries in the our newly created table.

Let's observe the Update Trigger:

--Test SQL Server Trigger for UPDATE

-- clear out table
TRUNCATE TABLE dbo.Results;

-- issue an update command
UPDATE dbo.ThereIsATriggerOnThisTable SET col1 = 'AFTER';

-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

Update trigger created new entries in the newly created table. Here we have deleted the previous records from the newly created table.

Let's observe for Delete table:

--Test SQL Server Trigger for DELETE

-- clear out table
TRUNCATE TABLE dbo.Results;

-- issue a delete command
DELETE dbo.ThereIsATriggerOnThisTable;

-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

Here you can see that we have deleted the record from the stock table and the Delete trigger inserted a new row in the newly created table.

Note: you can comment on the truncate table which removes the previous data from the table (Results) which we created for the reference point of view i.e. if any Insert, Update or Delete operation performs on the stock table (ThereIsATriggerOnThisTable) then related entries will be added in this table.

Thanks.