Skip to main content

SQL Server Triggers

 A trigger is a spacial type of stored procedure which is created on a particular table and it gets executed  in response to certain events (insert, update, delete) on that table. It is a database object which is bound to a table. You can not invoke it explicitly. SQL Server engine executes it implicitly when an event occurs on that table.

In SQL server there are two types of triggers.
1. After Trigger (For Trigger)
2. Instead of Trigger

After Trigger

After trigger is also called as For trigger. It gets fired after an insert or delete or update on a table.

Example: After a new employee record is inserted in employee table you want to insert a record in your Audit table saying "A new employee with name XYZ is added at 8 Aug 2017 5:17 PM".

 Before demonstrating this, let me explain about Magic tables.
Magic tables in SQL Server:

 -Magic tables are temporary tables created by SQL server internally every time when a DML action is performed on a table with trigger . There are two magic tables
1. Inserted
2. Deleted

- These tables get created with same structure as like base table. It is not possible to see these table directly. These can only be accessed through triggers.

-When a new record inserted in base table, SQL server inserts a replica of  the record in 'Inserted 'table.

-When a record deleted from base table, SQL server inserts a replica of the deleted record in 'deleted' table.

Now to see how to use Triggers let's first create a table 'Employee' and insert some records into it
The Employee table now looks like this
Next, create an Audit table say 'Employee_Audit'
Now create an After trigger on Employee table to insert a record into Employee_Audit table automatically after insertion of a new record into 'Employee' table
Try to insert a new record int Employee table.

Check the audit table!!!!


Instead of Trigger

Instead of trigger is executed automatically in place of an insert or delete or update on a table.

Example: When any one tries to delete an employee's record from  employee table you want to show an error message "You are not permitted to perform this action"; instead of the actual delete action.


Labels:

Comments

Post a Comment

Popular posts from this blog

Interview Questions and Answers on SQL Server View

    W hat is a View?        Views are virtual tables that hold data from one or more tables. It is a query stored in the database as object. A view does not contain any data itself, it is a set of queries that are applied to one or more tables that are stored within the database as an object   What is the use of view? It allows you to reuse code without  having to write the same complex SQL code over and over. Views are used for security purposes in databases. Views restrict the user from viewing certain columns and rows. In other words, using a view we can apply the restriction on accessing specific rows and columns for a specific user  Can we use ORDER BY clause inside a view?   The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is specified. Can we update a view?       If the view contains joins between mult...
Post a Comment
Read more

SQL Server- CHAR,VARCHAR, NVARCHAR

SQL Server String Data Types CHAR CHAR is a fixed data type. So when a column is declared as CHAR, irrespective of  the actual characters stored in that variable/column, it will use all the declared space. In the above example you can see that even though the actual length of the variable is 6, It occupies 30 bytes of space.This is because when the variable @name is declared as CHAR(30), 30 bytes of space got reserved for the variable.  Also, you can observe that CHAR type column uses 1 byte of space to store each character. Summary: 1. It is a fixed data type 2. Occupies 1 byte of space for each character 3. It is used to store non-unicode character. VARCHAR VARCHAR is a variable length data type. So when a variable or column is declared as VARCHAR, irrespective of declared space, it will use only space require to store all characters. In the above example you can see that even though the declare...
Post a Comment
Read more

SQL Server difference between DELETE, TRUNCATE, DROP

DELETE DELETE removes some specific rows (with WHERE clause) or all rows (without WHERE clause) The operation can be rolled back.    DELETE is a DML command. This operation cause all DELETE triggers on table to fire. Conditions like WHERE clause can be used. Note: when we issue a DELETE command then all data get copied into ROLLBACK Tablespace first. Then delete operation get performed. That is why when you type ROLLBACK after deleting table, you can get back the data (The system get it for you from the Rollback Tablespace) TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back. TRUNCATE is a DDL command. No triggers will be fired. Conditions like WHERE clause cannot be used with it. TRUNCATE is faster than DELETE. Note: when you issue a TRUNCATE command, it removes data directly without copying it into the Rollback Tablespace. So it is faster than DELETE. Also because of the same reason the data dele...
Post a Comment
Read more