آموزش کامل تریگرها، رویه های ذخیره شده و توابع در SQL

در این مقاله یک آموزش جامع از تریگرها، رویه های ذخیره شده و توابع در SQL همراه با ذکر جزئیات و مثال ارائه می دهیم. تریگرها به طور خودکار در پاسخ به رویدادهای خاص (مانند INSERT، UPDATE، DELETE) اجرا می‌شوند و تضمین می‌کنند که وظایف به طور مداوم و بدون دخالت دستی انجام می‌شوند.

رویه های ذخیره شده منطق تجاری پیچیده را در بلوک‌های کد قابل استفاده مجدد گنجانده و افزونگی را کاهش می‌دهد و توابع بلوک‌های کد قابل استفاده مجدد را ایجاد کنید که وظایف خاصی را انجام می‌دهند و مقادیر را برمی‌گردانند و پرس و جوهای پیچیده را ساده می‌کنند.

1. تریگرها:

Triggerها رویه‌های ذخیره‌شده خاصی هستند که به‌طور خودکار زمانی که رویدادهای خاصی در پایگاه داده رخ می‌دهند، اجرا می‌شوند. آنها برای حفظ یکپارچگی داده ها و اجرای قوانین تجاری استفاده می شوند.

انواع محرک ها:

• DML Triggers: در پاسخ به رویدادهای زبان دستکاری داده ها (DML) مانند INSERT، UPDATE، و DELETE اجرا می شود.
• DDL Triggers: در پاسخ به رویدادهای زبان تعریف داده (DDL) مانند CREATE، ALTER و DROP اجرا می شود.
• Logon Triggers: زمانی که کاربر وارد پایگاه داده می شود اجرا می شود.

مثال:

بیایید یک ماشه ساده DML ایجاد کنیم که حذف‌ها را از جدول کارکنان ثبت می‌کند.

CREATE TRIGGER log_deletion
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deletion_log (employee_id, deleted_at)
    VALUES (OLD.employee_id, NOW());
END;

در این مثال، هرگاه ردیفی از جدول کارکنان حذف شود، ورودی مربوطه در جدول deletion_log ایجاد می شود.

تریگرهای پیشرفته:

تریگرها ابزارهای قدرتمندی برای خودکارسازی وظایف در پاسخ به تغییرات در پایگاه داده شما هستند.

تریگرهای پیچیده:

• Multiple Actions Trigger: یک ماشه ایجاد می کند که چندین عمل را انجام دهد. به عنوان مثال، به روز رسانی جدول حسابرسی و ارسال هشدار.

مثال:

در اینجا یک راه‌انداز پیچیده‌تر وجود دارد که جدول حسابرسی را به‌روزرسانی می‌کند و هنگام به‌روزرسانی یک ردیف در جدول کارکنان، ایمیلی ارسال می‌کند:

CREATE TRIGGER audit_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Insert into audit table
    INSERT INTO employee_audit (employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
    -- Send an email (pseudo code for illustrative purposes)
    CALL send_email('admin@example.com', 'Employee Update', 
                    CONCAT('Employee ID ', OLD.employee_id, ' salary updated from ', 
                    OLD.salary, ' to ', NEW.salary));
END;

2. رویه های ذخیره شده:

رویه های ذخیره شده مجموعه ای از دستورات SQL هستند که می توانند در سرور پایگاه داده ذخیره و اجرا شوند. آنها به کاهش ترافیک شبکه و بهبود عملکرد کمک می کنند.

نحو:

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

مثال:

در اینجا یک رویه ذخیره شده ایجاد شده است که همه مشتریان را از یک شهر خاص انتخاب می کند:

CREATE PROCEDURE SelectCustomersByCity @City NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Customers WHERE City = @City;
END;

برای اجرای رویه ذخیره شده:

EXEC SelectCustomersByCity @City = 'London';

رویه های ذخیره سازی پیشرفته:

رویه‌های ذخیره‌شده می‌توانند شامل زبان کنترل جریان، مانند دستورات شرطی (IF، CASE) و حلقه‌ها (WHILE، FOR) باشند.

مثال:

در اینجا یک رویه ذخیره شده است که پاداش را برای کارکنان بر اساس رتبه عملکرد آنها محاسبه می کند و حقوق آنها را به روز می کند:

CREATE PROCEDURE CalculateBonus
AS
BEGIN
    DECLARE @EmployeeID INT, @Rating INT, @Salary DECIMAL(10, 2), @Bonus DECIMAL(10, 2);   

    DECLARE employee_cursor CURSOR FOR
    SELECT employee_id, performance_rating, salary FROM employees;    

    OPEN employee_cursor;   

    FETCH NEXT FROM employee_cursor INTO @EmployeeID, @Rating, @Salary;   

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Bonus = CASE 
                        WHEN @Rating = 5 THEN @Salary * 0.10
                        WHEN @Rating = 4 THEN @Salary * 0.05
                        ELSE 0
                     END;
        UPDATE employees SET salary = salary + @Bonus WHERE employee_id = @EmployeeID;
        
        FETCH NEXT FROM employee_cursor INTO @EmployeeID, @Rating, @Salary;
    END;  

    CLOSE employee_cursor;
    DEALLOCATE employee_cursor;
END;

3. توابع:

از توابع برای انجام عملیات و برگرداندن یک مقدار استفاده می شود. آنها می توانند اسکالر (برگرداندن یک مقدار واحد) یا با ارزش جدول (برگرداندن یک جدول) باشند.

نحو:

CREATE FUNCTION function_name
RETURNS data_type
AS
BEGIN
    -- SQL statements
    RETURN value;
END;

مثال:

در اینجا یک تابع اسکالر وجود دارد که کل فروش را برای یک محصول مشخص محاسبه می کند:

CREATE FUNCTION GetTotalSales (@ProductID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @TotalSales DECIMAL(10, 2);
    SELECT @TotalSales = SUM(quantity * price) FROM Sales WHERE product_id = @ProductID;
    RETURN @TotalSales;
END;

برای فراخوانی تابع:

SELECT dbo.GetTotalSales(1);

توابع پیشرفته:

توابع را می توان نه تنها برای برگرداندن مقادیر منفرد بلکه برای برگرداندن جداول و انجام محاسبات پیچیده استفاده کرد.

مثال:

در اینجا یک تابع با ارزش جدول وجود دارد که همه کارکنانی را که به یک هدف فروش دست یافته اند برمی گرداند:

CREATE FUNCTION EmployeesMeetingSalesTarget (@Target INT)
RETURNS TABLE
AS
RETURN
(
    SELECT employee_id, name, total_sales
    FROM employees
    WHERE total_sales >= @Target
);

برای استفاده از این تابع:

SELECT * FROM EmployeesMeetingSalesTarget(100000);

ترکیب مفاهیم:

مثال:

بیایید تریگرها، رویه‌های ذخیره‌شده و توابع را در یک سناریو ترکیب کنیم. فرض کنید سیستمی داریم که در آن پاداش های کارکنان هر زمان که فروش انجام می شود محاسبه و ثبت می شود.

1. تابع محاسبه پاداش:

CREATE FUNCTION CalculateEmployeeBonus (@SalesAmount DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10, 2);
    SET @Bonus = @SalesAmount * 0.10; -- 10% of sales as bonus
    RETURN @Bonus
END;

2. رویه ذخیره شده برای اعمال پاداش:

CREATE PROCEDURE ApplyBonus
@EmployeeID INT, @SalesAmount DECIMAL(10, 2)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10, 2);
    SET @Bonus = dbo.CalculateEmployeeBonus(@SalesAmount);
    UPDATE employees SET bonus = bonus + @Bonus WHERE employee_id = @EmployeeID;
    INSERT INTO bonus_log (employee_id, sales_amount, bonus, applied_at)
    VALUES (@EmployeeID, @SalesAmount, @Bonus, GETDATE());
END;

3. ماشه برای اعمال پاداش پس از فروش:

CREATE TRIGGER after_sale
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    CALL ApplyBonus(NEW.employee_id, NEW.amount);
END;

در این سناریو، هنگامی که یک فروش جدید ثبت می‌شود، تریگر after_sale رویه ذخیره شده ApplyBonus را فراخوانی می‌کند، که سپس از تابع CalculateEmployeeBonus برای تعیین پاداش و اعمال آن در رکورد کارمند استفاده می‌کند.

این نمونه‌های پیشرفته نشان می‌دهند که چگونه می‌توان از تریگرها، رویه‌های ذخیره‌شده و توابع با هم برای ساخت راه‌حل‌های پایگاه داده خودکار و قوی استفاده کرد.