[ Join Now (FREE) | Log In]

How to create a trigger in SQL Server Database

by Jagadish Pulakhandam on 9/23/2011 9:21:44 AM
Rated 0 from 0 votes
Brief: Demonstrates on creating a simple trigger which gets fired for every insert/update on table (which calculates and updates the column values automatically)
Posted to: Microsoft SQL Server - Quick Learn ("How to"s for Developers)
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Attached video demonstrates the following:
  • Create a simple trigger (AFTER Trigger) from the scratch
  • The trigger gets fired (executed) automatically whenever a row gets inserted/updated in the table
  • The trigger automatically calculates and updates the values in a couple of columns when row values are inserted/changed.
Following is the sample table used for this demonstration:



Following is the code used for the above:

01.CREATE TRIGGER trgStudMarks
02.   ON  StudMarks
03.   AFTER INSERT, UPDATE
04.AS
05.BEGIN
06.    SET NOCOUNT ON;
07.     
08.    UPDATE a
09.    SET a.TotMarks = a.MathsMarks + a.StatsMarks + a.CompScienceMarks,
10.        a.AvgMarks = (a.MathsMarks + a.StatsMarks + a.CompScienceMarks) / 3.0
11.    FROM StudMarks a
12.    JOIN Inserted I ON a.Regdno = I.Regdno
13. 
14.END



Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout


Video/Screen Recording (may not have audio narration/annotations)
You need to Login or Join for FREE to download the following