DATABASE TRIGGER

                                                             DATABASE  TRIGGER

Summary

  • A Trigger executes automatically when data changes.
  • It can respond to INSERT, UPDATE, and DELETE events.
  • The inserted pseudo-table contains new values.
  • The deleted pseudo-table contains old values.
  • Triggers are commonly used for auditing, validation, logging, and enforcing business rules.

CREATE TABLE Employee

(

    EmpID INT PRIMARY KEY,

    EmpName VARCHAR(50),

    Salary DECIMAL(10,2)

);

 

 

INSERT INTO Employee

VALUES

(101, 'Amit', 30000),

(102, 'Rahul', 35000),

(103, 'Priya', 40000);

 

 

SELECT * FROM  EMPLOYEE

 



CREATE TABLE EmployeeAudit

(

    AuditID INT IDENTITY(1,1) PRIMARY KEY,

    EmpID INT,

    OldSalary DECIMAL(10,2),

    NewSalary DECIMAL(10,2),

    UpdatedDate DATETIME

);

 

CREATE  TRIGGER 


CREATE TRIGGER trg_UpdateSalary

ON Employee

AFTER UPDATE

AS

BEGIN

    INSERT INTO EmployeeAudit

    (

        EmpID,

        OldSalary,

        NewSalary,

        UpdatedDate

    )

    SELECT

        d.EmpID,

        d.Salary,

        i.Salary,

        GETDATE()

    FROM deleted d

    INNER JOIN inserted i

    ON d.EmpID = i.EmpID;

END;

 

 PERFORM UPDATE  QUERY 

 

 

UPDATE Employee

SET Salary = 45000

WHERE EmpID = 102;

 

SELECT * FROM EmployeeAudit;






INSERT  TRIGGER 



CREATE TRIGGER trg_InsertEmployee
ON Employee
AFTER INSERT
AS
BEGIN
    PRINT 'New Employee Added Successfully';
END;


INSERT INTO Employee
VALUES (104, 'Neha', 38000);



                DELETE  TRIGGER

CREATE TRIGGER trg_DeleteEmployee
ON Employee
AFTER DELETE
AS
BEGIN
PRINT 'Employee Record Deleted';
END;


DELETE FROM Employee
WHERE EmpID = 101;
























Comments

Post a Comment

Popular posts from this blog

Python Introduction

PYTHON PROGRAMING https://thonny.org/ Python supports multiple programming paradigms, including object-oriented, procedural, and functional programming . It provides a rich standard library and a large ecosystem of third-party packages, which helps developers to build applications faster and more efficiently. Python is platform-independent, meaning Python programs can run on Windows, Linux, and macOS without modification. Due to its flexibility and powerful features, Python is widely used in fields such as: ·          Web Development ·          Data Science & Data Analytics ·          Artificial Intelligence & Machine Learning ·          Automation & Scripting ·          Desktop Application Development · ...
Read more

STORE PROCEDURE BASIC PROGRAMMING - PART 1

  1. Create a Stored Procedure CREATE PROCEDURE sp_GreetUser AS BEGIN PRINT 'Welcome to SQL Server Stored Procedure Programming' ; END ; ================================================================================ Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ========================================================================== Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ======================...
Read more

STORE PROCEDURE ADVANCE CONCEPT - PART 2

  List All Stored Procedures SELECT name FROM sys.procedures; ================================================================================ Stored Procedure Calling Another Stored Procedure with Parameters in SQL Server You cannot create a stored procedure inside another stored procedure, but you can call one stored procedure from another and pass parameters . Step 1: Create Table CREATE TABLE Employee ( EmpID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , EmpName VARCHAR ( 100 ), Salary DECIMAL ( 10 , 2 ) ); Step 2: Insert Sample Data INSERT INTO Employee(EmpName, Salary) VALUES ( 'Nilesh Gupta' , 50000 ), ( 'Rahul Sharma' , 45000 ), ( 'Priya Patel' , 55000 ); ====================================================================================== Procedure 1: Get Employee by ID CREATE PROCEDURE sp_GetEmployeeByID ( @EmpID INT ) AS BEGIN SELECT * FROM Employee WHERE EmpID = @EmpID; END ; GO Execute EXEC sp_GetEmployeeByI...
Read more