Triggers to Enforce Data Consistency in SQL Server

Question

Hi friends I want to know how to use triggers to enforce data consistency in SQL Server. Thank you.

MuthuMari M · Answer

Hi, An important part in database designing and planning is deciding way to enforce the integrity of the data. Data integrity refers to the consistency and accuracy of data that is stored in your database.Enforcing data integrity ensures the quality of the data in your database. Three types of data integrity are: Column Integrity:- Column integrity specifies a set of data values that are valid for a column and determines whether to allow null values or not. Column integrity is often enforced by restricting the data type, format, or range of possible values allowed in a column. Entity Integrity:- Entity integrity requires that all rows in a table have a unique identifier, known as the primary key value. Referential Integrity:- Referential integrity ensures that the relationships between the primary keys and foreign keys are always maintained. You can use data types,constraints,defaults,triggers to enforce the data integrity in your database. thanks. If this post is useful then mark it as " Accepted Answer "

Senthilkumar · Answer

Hi, Data consistency means Here what you look for? The column in the table can be set as following. - Specific data type - Not null constrains - Auto increment column - Uniqueness of the column values - Check constraints - Default constraints It can be done at the time of table creation. what else you expect here?

Satyapriya Nayak · Answer

Hi Vipendra, A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made ussing INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data. Creation of Triggers Triggers are created with the CREATE TRIGGER statement. This statement specifies that the on which table trigger is defined and on which events trigger will be invoked. To drop Trigger one can use DROP TRIGGER statement. Code: SQL CREATE TRIGGER [ owner. ] trigger_name ON [ owner. ] table_name FOR [ INSERT / UPDATE / DELETE ] AS IF UPDATE ( column_name ) [{ AND / OR } UPDATE ( COLUMN_NAME ) ... ] { sql_statements } Trigger rules and guidelines A table can have only three triggers action per table : UPDATE ,INSERT,DELETE. Only table owners can create and drop triggers for the table.This permission cannot be transferred.A trigger cannot be created on a view or a temporary table but triggers can reference them. A trigger should not include SELECT statements that return results to the user, because the returned results would have to be written into every application in which modifications to the trigger table are allowed. They can be used to help ensure the relational integrity of database.On dropping a table all triggers associated to the triggers are automatically dropped . The system stored procedure sp_depends can be used to find out which tables have trigger on them. Following sql statements are not allowed in a trigger they are:- Code: SQL ALL CREATE statements, ALL DROP statements, ALTER TABLE AND ALTER DATABASE , TRUNCATE TABLE , GRANT AND REVOKE , UPDATE STATISTICS,RECONFIGURE, LOAD DATABASE AND LOAD TRANSACTION, ALL DISK statements, SELECT INTO INSERT trigger When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out. The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables. DELETE trigger When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table. UPDATE trigger When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated. Syntax Code: SQL IF UPDATE CREATE TRIGGER trigger1 ON member FOR UPDATE AS IF UPDATE ( last_name ) BEGIN RAISEERROR ( 'Transaction cannot be processed' ) ROLLBACK TRANSACTION END Multi-row trigger A multi-row insert can occur from an INSERT with a SELECT statement.Multirow considerations can also apply to multi-row updates and multi-row deletes. Code: SQL CREATE TRIGGER adult_insert ON adult FOR INSERT AS DECLARE @rcnt int SELECT @rcnt = @@rowcount IF ( SELECT COUNT ( * ) FROM member,inserted WHERE member.mem_num = inserted.mem_num ) = 0 BEGIN PRINT 'Transaction not processed' PRINT 'No entry for this member' ROLLBACK TRANSACTION END IF ( SELECT COUNT ( * ) FROM member,inserted WHERE member.mem_num = inserted.mem_num ) @rcnt BEGIN PRINT 'Not all adults have an entry in the member table' PRINT 'Multi-row insert transaction has been rolled backed' ROLLBACK TRANSACTION END Please refer the below links http://www.go4expert.com/forums/showthread.php?t=15510 http://www.c-sharpcorner.com/uploadfile/shashikantray/create-delete-and-update-triggers-in-a-database/default.aspx http://www.c-sharpcorner.com/uploadfile/37db1d/creating-and-managing-triggers-in-sql-server-20052008/default.aspx Thanks