What is Trigger?
A trigger is a database object that is bound to a table. It is a special type of stored procedure that executes automatically when any DML operations are performed.
Microsoft SQL Server 2000 supports two different types of triggers namely INSTEAD OF and AFTER Trigger. Both this trigger is different in the execution.
It supports Multiple after Trigger also. Triggers cannot be created on the temporary tables.
After Trigger:
After trigger automatically get executed before the transaction get completed or rollback. After trigger created only on tables not in views. This is very useful to maintain the data integrity between the tables.
For my convenient I created some tables to show the demo code
Employee Table
CREATE TABLE employee (
SAPID int NOT NULL ,
FName varchar (50) NOT NULL ,
MName varchar (50) NULL ,
LName varchar (50) NOT NULL ,
Email varchar (50) NOT NULL ,
CONSTRAINT SAPID_FK FOREIGN KEY(SAPID)
REFERENCES department(SAPID)
)
Department Table
CREATE TABLE department (
SAPID int identity(1,1) primary key,
UserName varchar (50) NULL ,
Password varchar (50) NULL
)
I am inserting the values to the tables
INSERT INTO department(USERNAME, PASSWORD) values('sathiya','india')
INSERT INTO department(USERNAME, PASSWORD) values('Thiya','hcl')
INSERT INTO department(USERNAME, PASSWORD) values('saravanan','papap')
INSERT INTO department(USERNAME, PASSWORD) values('vijay','helloworld')
INSERT INTO employee(SAPID,FNAME,LNAME,EMAIL) values(1,'sathiya','sivam','sathiya@gmail.com')
INSERT INTO employee(SAPID,FNAME,LNAME,EMAIL) values(2,'Thiya','PT','mouse@gmail.com')
INSERT INTO employee(SAPID,FNAME,LNAME,EMAIL) values(3,'saravana','se','saravana@gmail.com')
INSERT INTO employee(SAPID,FNAME,LNAME,EMAIL) values(4,'vijay','kl','vijay@gmail.com')
AFTER Triggers
CREATE TRIGGER aftertrigger
ON department
FOR INSERT
AS
Print ('After Tigger is initiated')
GO
BEGIN TRANSACTION
DECLARE @ERR INT
INSERT INTO department(USERNAME, PASSWORD)
VALUES('Ram','india')
SET @ERR = @@Error
IF @ERR = 0
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
--End of the program
INSTEAD OF Triggers
This trigger gets executed automatically before the primary key and the foreign key column constraint are checked. Whereas the traditional trigger (After Trigger) get executes automatically after all the check constraints are checked.
After Trigger
CREATE TRIGGER afterinsert
On employee
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger is initiated')
END
After Trigger conflicted column
INSERT INTO employee(SAPID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')
The SAPID 100 not present in the department table.
Error Message
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'SAPID_FK'. The conflict occurred in database 'pubs', table 'department', column 'SAPID'.
The statement has been terminated.
Same Task With Instead Of Trigger
Instead trigger example
Create Trigger InsteadInsert
On employee
INSTEAD OF INSERT
AS
BEGIN
Print ('INSTEAD OF Trigger Initiated')
END
Example
INSERT INTO employee(SAPID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')
INSTEAD OF Trigger Initiated
(1 row(s) affected)
Conclusion
Triggers are a powerful tool that can be used to enforce the business integrity or data integrity automatically when the data is modified. Triggers should be used to maintain the data integrity only if you are not capable to enforce the data integrity using CONSTRAINTS, RULES.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|