 |
How to write a trigger for INSERT, UPDATE or DELETE operation ?
|
| Total Hit (2384) |
Here is very basic code to write trigger for INSERT, UPDATE or DELETE operation on a table. Internally SQL Server manages two tables (inserted and deleted) to perform trigger operations.
All inserted records can be retrived by quering "inserted" table
«code LangId=6»select * from inserted«/cod
....Read More |
Rating
 |
|
|
|
|
|
Conditional Firing of Triggers.
|
| Total Hit (2134) |
This sample shows how you can conditionally fire triggers. This example shows how to suppress the trigger logic while inserting data from a SP & allowing other inserts to go through fine. A SQL6x/70/2000 version of the example is shown first & another one using the SQL70/2000 cursor function is show
....Read More |
Rating
 |
|
|
|
Code to find out the statement that caused the trigger to fire!
|
| Total Hit (1873) |
Sometimes you may want to find out what exact statement that updated your table. Or you may want to find out how the WHERE clause of the DELETE statement (Executed by someone) looked like.
DBCC INPUTBUFFER can provide you with this kind of information. You can create a trigger on your table, tha
....Read More |
| Rating
|
|
|
|
Quickly Disable Constraint and Triggers
|
| Total Hit (2055) |
In a previous article, I covered how to use the sp_msforeachtable system stored procedure to look through a list of tables and perform an action on them.
When loading data, nothing can be more frustrating than having to deal with stubborn constraints and triggers. This is especially true when you k
....Read More |
| Rating
|
|
|
|
INSTEAD OF Triggers
|
| Total Hit (1931) |
AFTER triggers (also known as FOR triggers) execute following the triggering action, such as an insert, update, or delete. For example, an AFTER trigger on an Employees table will fire after an UPDATE statement has been executed against the Employees table. Thus, the trigger does not fire until the
....Read More |
| Rating
|
|
|
|
Updating Derived Columns using Triggers
|
| Total Hit (1693) |
INSTEAD OF triggers are also commonly used to UPDATE the base columns in calculated columns. For example, assume that a view exists called vwOrdersOrderDetailsProducts as shown here:
«Code LangId=6»
CREATE VIEW vwOrdersOrderDetailsProducts
AS
SELECT o.OrderID,
o.OrderDate,
....Read More |
| Rating
|
|
|
|
Checking for Changes within trigger
|
| Total Hit (2075) |
The UPDATE and COLUMNS_UPDATED functions are available within both types of triggers to allow the trigger to determine which columns were modified by the triggering action statement. For example, the following trigger prevents any modifications to the lastname column in the Employees table. Here, th
....Read More |
| Rating
|
|
|
|
Exploring SQL Server Triggers
|
| Total Hit (2715) |
Triggers are one of the core tools available in relational databases such as SQL Server™ 2000. As one of the mainstays of SQL Server database programming, triggers also happen to be one of the topics that I get most of the questions about. In this month's installment of Data Points, I will explore t
....Read More |
| Rating
|
|