Sunday, May 6, 2018

Tiggers

Tiggers are special type of store procedures that gets automatically executed when curtain events occurs on database server

Tiggers are classified into 3 types


DML triggers gets fired  when users  manipulate the data in table  using (insert, update, delete ) statements

Synatx :
Create Trigger [NAME]
on [TABLE]
[FOR | AFTER | INSTEAD OF]
As
BEGIN
[SQL CODE]


END

DML triggers are of two types  instead of triggers and after trigger
Instead of trigger: Are type of triggers which would  execute  the trigger code instead of DML event (insert delete update)

Instead of insert tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO
 CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] varchar(30) Null
) ON [PRIMARY]
GO

CREATE Trigger dbo.person_insteadoftrigger_insert
ON  [dbo].[person]
INSTEAD of INSERT
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Instead_of_insert'
from inserted
End


SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)
INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

(1 row affected)

(1 row affected)

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Instead_of_insert

(1 row affected)

Instead of delete tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO
CREATE trigger dbo.person_insteadoftrigger_delete
ON  [dbo].[person]
INSTEAD of DELETE
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Instead_of_delete'
from deleted
End

 INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

DELETE FROM DBO.person
WHERE BusinessEntityID = 1
(1 row affected)

(1 row affected)

We can see below here that, when we have tried to delete the row from the person table, it instead of deleting from person table it inserted a record in our audit table 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Instead_of_delete

(1 row affected)

Instead of update tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO

CREATE trigger dbo.person_insteadoftrigger_update
ON  [dbo].[person]
INSTEAD of update
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Before_update'
from deleted
UNION
select [BusinessEntityID],[FirstName],[LastName],'After_update'
from inserted
End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

Update dbo.person
set FirstName = 'SQL SERVER'
where BusinessEntityID = 1

(2 rows affected)

(1 row affected)

We can see below here that, when we have tried to update the row from the person table, it instead of updating from person table it inserted a record in our audit table before and after value of what begin updated 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Instead_of_delete

(1 row affected)


After trigger  is the one which execute trigger code after executing DML action (insert update delete )


After insert tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO
 CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] varchar(30) Null
) ON [PRIMARY]
GO

CREATE Trigger dbo.person_After_insert
ON  [dbo].[person]
For INSERT
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'After_insert'
from inserted
End


SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit

BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)
INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')
(1 row affected)

(1 row affected)

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   After_insert

(1 row affected)

After delete tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO
CREATE Trigger dbo.person_After_Delete
ON  [dbo].[person]
For DELETE
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'After_delete'
from deleted
End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')


SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

DELETE FROM DBO.person
WHERE BusinessEntityID = 1
(1 row affected)

(1 row affected)

We can see below here that, when we have tried to delete the row from the person table, it deleted from person table and  it inserted a record in our audit table 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------

(0 rows affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   After_delete


(1 row affected)

After update tigger, To demonstrate lets create two tables (1. main table 2. audit table to capture information )  and tigger on the primary table 

 IF OBJECT_ID('dbo.person') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[person](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL
) ON [PRIMARY]
GO



IF OBJECT_ID('dbo.Person_Audit') is not null
DROP TABLE [dbo].[person]
GO

CREATE TABLE [dbo].[Person_Audit](
       [BusinessEntityID] [int] NULL,
       [FirstName] [varchar](30) NULL,
       [LastName] [varchar](30) NULL,
       [Audit_info] [varchar](30) NULL
) ON [PRIMARY]
GO

CREATE Trigger dbo.person_After_update
ON  [dbo].[person]
For update
AS
Begin
insert into dbo.person_audit
select [BusinessEntityID],[FirstName],[LastName],'Before_update'
from deleted
union
select [BusinessEntityID],[FirstName],[LastName],'After_update'
from inserted

End

INSERT INTO DBO.person VALUES (1,'SANDEEP','RAIN_OR_SINE')

SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------

(0 rows affected)

Update dbo.person
set FirstName = 'SQL SERVER'
where BusinessEntityID = 1

(2 rows affected)

(1 row affected)

We can see below here that, when we have tried to update the row from the person table, it updated row from person table and  it inserted a records in our audit table before and after value of what begin updated 
SELECT * FROM DBO.person
SELECT * FROM DBO.Person_Audit
BusinessEntityID FirstName                      LastName
---------------- ------------------------------ ------------------------------
1                SQL SERVER                     RAIN_OR_SINE

(1 row affected)

BusinessEntityID FirstName                      LastName                       Audit_info
---------------- ------------------------------ ------------------------------ ------------------------------
1                SANDEEP                        RAIN_OR_SINE                   Before_update
1                SQL SERVER                     RAIN_OR_SINE                   After_update


(2 rows affected)

DDL



  
Posted by at
Labels:

1 comment:

  1. Donald clerkApril 8, 2022 at 1:23 AM

    ARE YOU TIRED OF SEEKING FOR LOAN AND MORTGAGES, HAVE YOU BEEN TURNED DOWN CONSTANTLY BY ANY BANK AND ANY FINANCIAL INSTITUTIONS.

    Here comes a very good opportunity for you to get your loan and end your hard times, we have provided over $100 millions dollars in business loans to over 10,000 business owners worldwide. We use our own designated risk technology to provide you with the right business loan so you can grow your business and live a happy family again. Our services are fast and reliable to all our clients, we ensure that our loans are approved within 24 hours of successful application. We offer loans from a minimum range of $5,000 to a maximum of $500 million.

    Do you find yourself in a bit of trouble with unpaid bills and don’t know which way to go or where to turn? What about finding a reputable Debt Consolidation firm that can assist you in reducing monthly installment so that you will have affordable repayment options as well as room to breathe when it comes to the end of the month and bills need to get paid? have you cried for many years because of poverty lifes, we create this organization to help the you rich and start a good business and bring your dreams to a reality.

    Contact the general manager directly with the following details. Email quickloan9888@gmail.com

    Our services include following:

    * Student Loans
    * Truck Loans
    * Personal Loans
    * Business loan
    * Debt pay loans
    * Car Loans
    * Hotels Loans
    * Mortgage
    * Refinancing Loans etc.

    We give you a loan with an interest rate of 2% 30 years to pay back.

    NOTE: Bear in mind that it will only take less than 24 Hours to process your file is 100% Guaranteed no matter your Credit Score, contact us today and end your depth problems.

    ReplyDelete

Subscribe to: Post Comments (Atom)