Triggers in SQL Server
Triggers are stored programs, which are automatically executed or fired when some events occur.Triggers are, in fact, written to be executed in response to any of the following events −
* A database manipulation (DML) statement: (DELETE, INSERT, or UPDATE)
* A database definition (DDL) statement: (CREATE, ALTER, or DROP).
* A database operation: (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is associated.
Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Where,
To start, we will use the CUSTOMERS table:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ranith | 32 | Ahmedabad | 2000.00 |
| 2 | Krupa | 25 | Delhi | 1500.00 |
| 3 | kaulik | 23 | Kota | 2000.00 |
| 4 | Chaitan | 25 | Mumbai | 6500.00 |
+----+----------+-----+-----------+----------+
The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old and new values:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
When the above code is executed at the SQL prompt, it produces the following result:
Trigger created.
The following points need to be considered here −
Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Smritika', 22, 'HP', 7500.00 );
When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result:
Old salary:
New salary: 7500
Salary difference:
Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table:
Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table:
UPDATE customers
SET salary = salary + 1500
WHERE id = 1;
UPDATE customers
SET salary = salary + 1500
WHERE id = 1;
When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result:
When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result:
Old salary: 2000
New salary: 3500
Salary difference: 1500
Old salary: 2000
New salary: 3500
Salary difference: 1500
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials