PL/SQL Triggers - CREATE TRIGGER (Transact-SQL) - Triggers SQL Server смотреть онлайн

What is a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers
There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

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 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 could be defined on the table, view, schema, or database with which the event is associated.

Triggers can be written for the following purposes:

1. Generating some derived column values automatically
2. Enforcing referential integrity
3. Event logging and storing information on table access
4. Auditing
5. Synchronous replication of tables
6. Imposing security authorizations
7. Preventing invalid transactions

--Learn How to create and use Trigger

1)Create Table Structures.

-- First create table Employee_Demo
CREATE TABLE Employee_Demo
(
Emp_ID int identity,
Emp_Name varchar(55),
Emp_Sal decimal (10,2)
)
-- Now Insert records for testing
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);

select * from Employee_Demo

--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo
--this other table will be used for trigger operation effect
create table Employee_Demo_Audit
(
Emp_ID int,
Emp_Name varchar(55),
Emp_Sal decimal(10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

select * from Employee_Demo_Audit
--No any record found in Employee_Demo_Audit table



2)Create Trigger.

-- Create trigger on table Employee_Demo for Insert statement
--Trigger will fire when any record inserted into Employee_Demo table.
--we can fetch value of inserted record into Employee_Demo table using 'inserted' variable.
--we create trigger such that any record inserted into Employee_Demo table then
--another record will auto matically inserted into Employee_Demo_Audit table
--with same value of Emp_ID,Emp_Name,Emp_Sal of Employee_Demo table.


CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT

AS
--declare required variables with variable name and its datatype.
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);

--get employeid from inserted records
--get employee name from inserted records
--get employee salary from inserted records
--assign value to empid,@empname,@empsal variables using select statement.

select @empid=i.Emp_ID,@empname=i.Emp_Name,@empsal=i.Emp_Sal from inserted i;


--testing text of audit action
set @audit_action='Inserted Record -- After Insert Trigger.';

--now insert into Employee_Demo_Audit.
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
----trigger end

--Trigger will be executed when any record inserted into Employee_Demo
--'inserted' its a varible that hold record data value which is inserted into Employee_Demo table.




3)Execute insert record query to run trigger.

--Now try to insert data in Employee_Demo table
--Emp_ID column of Employee_Demo is 'identity'. its value will be auto insert by system

insert into Employee_Demo(Emp_Name,Emp_Sal)values ('test1411',1000);
--Output will be

--when you insert any record into Employee_Demo table then trigger will be executed and
--that trigger will insert one record into Employee_Demo_Audit as per trigger logic.

--Thanks