How to create trigger and how insert, update SQL statements work.
Codes are below...
This video shows, how to add a trigger on your current tables. This trigger will work for updating another column in same table according to Position column value. Also, it will insert a log record to another table with related ID. SQL Server Management Studio has been used for creating trigger.
if OBJECT_ID ('dbo.trg_tblPersonal') is not null
DROP trigger dbo.trg_tblPersonal
go
CREATE TRIGGER dbo.trg_tblPersonal
ON dbo.tblPersonal
AFTER INSERT, UPDATE
AS
DECLARE @Salary numeric
DECLARE @Pos int
DECLARE @PerID int
DECLARE @Name nvarchar(100)
DECLARE @LastName nvarchar(100)
DECLARE @NameOrig nvarchar(100)
DECLARE @LastNameOrig nvarchar(100)
DECLARE @Log nvarchar(4000)
BEGIN
SELECT @Pos = Position, @PerID = PersonalID, @Name = Name, @LastName = LastName FROM inserted
SELECT @NameOrig = Name, @LastNameOrig = LastName FROM deleted
if (@Pos = 1)
set @Salary = 100.0 + 100 * 0.18
else
set @Salary = 200.0 + 500
if (@NameOrig <> @Name or @LastName <> @LastNameOrig)
begin
set @Log = N'Name or LastName has been changed...'
insert into dbo.tblLog (LogDescription) VALUES (@Log)
end
update dbo.tblPersonal set Salary = @Salary where PersonalID= @PerID
END
GO