Home » ASP » Article
|
|
| Viewed: 117783 times |
Rating (149 votes): |
|
2.4 out of 5 |
|
|
|
Creating an SQL Trigger (SQL Server 7/2000)
So what exactly is a trigger? Well, it's a piece of sql that is activitied when a certain event happens. A common usage is when a new record is added to a database, this triggers an action - for example, a new order triggers an email to the supplier. It can be very useful.
To relative SQL newbies triggers and stored procedures can seem a bit daunted, but you'll quickly see there's really nothing to them.
To create a trigger, go to the Enterprise Manager, and your database. Select the table that causes the event to happen, right click > All Tasks > Manage Triggers and select new from the drop down box.
Here we're going insert a new record into the table directory_update whenever a record is inserted into the directory table.
What this particular code does is inserts the latest record id - max(directory_id) - from the directory, and the current date, into the table directory_update. It does this whenever a new record is inserted into the directory.
I used something similar to this to create 'last updated' flags within a directory structure - but that's another article. For now, I hope that's been a useful introduction for you into the world of Triggers.
|
|
View highlighted Comments
 User Comments on 'Creating an SQL Trigger'
|
Posted by :
 Archive Import (pk) at 05:05 on Wednesday, April 09, 2003
|
right click > All Tasks > Manage Triggers
how u got this path???????
|
|
|
Posted by :
Archive Import (kumar narayanan) at 06:34 on Sunday, April 13, 2003
|
Hai
I need to know how to trigger mail to a user whenever a change is made in the table.
can you give some tips?
Regards
kumar
|
|
|
Posted by :
Archive Import (Rebekah) at 19:13 on Sunday, May 11, 2003
|
I really need help with an Update trigger that puts the current date in a changed record. I have used regular UPDATE, and also IF (COLUMNS_UPDATED()). Whenever I run either trigger, all records in the table are affected. I won't be able to identify the record being changed other than that it'll be the current record. Is there a way to write that in?
|
|
|
Posted by :
Archive Import (Sucharit) at 02:45 on Monday, May 26, 2003
|
Hi,
I need to know how to execute multiple stored procedures from a Trigger with checking the status code from each procedure.
|
|
|
|
|
Posted by :
Archive Import (sajs) at 06:24 on Tuesday, July 01, 2003
|
I need how to trigger database informations to the database user .
Can you give me tips?
Thanks
|
|
|
Posted by :
Archive Import (Asanga) at 05:52 on Wednesday, July 02, 2003
|
In my Ent.Manager I don't get "Manage Triggers" Did i miss something on Installation ?
|
|
|
Posted by :
Archive Import (man) at 12:05 on Wednesday, July 02, 2003
|
Hi,
I need to know how to trigger updated mails to a database users.
can you give some tips?
regardes
|
|
|
Posted by :
Archive Import (m asghar) at 01:50 on Monday, July 07, 2003
|
Hi,
Can any body solve my little problem.
I want just two things to happen
1- How i can create session type variable in SQL Server from vb,that is actually a user who logs in.
2-How can i know about the last inserted record in any particular table using trigger.
I want to access the created session in that trigger also.
|
|
|
Posted by :
Archive Import (PAVA) at 20:17 on Tuesday, July 22, 2003
|
ESTA PAGINA ES UNA TONTERA NO HAY NADA Y POR LO TANTO ES UNA MIERDA
|
|
|
Posted by :
Archive Import (PAVA) at 20:17 on Tuesday, July 22, 2003
|
ESTA PAGINA ES UNA TONTERA NO HAY NADA Y POR LO TANTO ES UNA MIERDA
|
|
|
Posted by :
Archive Import (Biju) at 06:19 on Tuesday, July 29, 2003
|
Can we call the trigger from VB? If it is possible, how and what is the command we will used for that.
i expect a valuable reply from you as soon as possible
|
|
|
Posted by :
Archive Import (aa) at 13:00 on Wednesday, August 06, 2003
|
I have the same problem, I don't get "Manage Triggers"
|
|
|
Posted by :
Archive Import (Imy) at 14:26 on Wednesday, August 27, 2003
|
This ain't no tutorial, you moron. Its a piece of crap.
|
|
|
Posted by :
Archive Import (Abhilash) at 06:29 on Monday, September 08, 2003
|
Hey Friends! your search for manage triggers end now here....
Actually first u select the database, then select the tables, select the table on which you wnat "trigger", it's basically right-click on the your table name. Enjoy rest
|
|
|
|
Posted by :
 itus2006 at 21:26 on Sunday, December 28, 2003
|
Thanks for your codes !
|
|
|
Posted by :
itus2006 at 04:21 on Thursday, February 12, 2004
|
I think that, trigger is very powerful in tables. But if you always use trigger in your application, an error has occur. So, You had better use trigger carefully.
Do you know about COM and Have you ever constructed your programs with COM ? it is easy to construct any programs by using COM model, of course you have known COM model clearly.
Contact :
itus2006@yahoo.com
or :
tdforum@operamail.com
|
|
|
Posted by :
pathak at 00:47 on Wednesday, March 03, 2004
|
i have this problem that i havent been able to solve for almost a week now.....
i am designing this SQL server backend & ASP frontend software....
how do i update several fields at a time concurrently from the ASP??? the update query needs a primary key for the row to be selected but that primary key is being generated by SQL server as a counter.....so i am just defining the variable where this primary key is being stored. <%=rs(call_id))%> call_id being 1,2,3... upon generation
how do i select attributes pertaining to a particular call_id so that i make changes in that row upon pressing the save button?
|
|
|
Posted by :
evraff at 06:08 on Tuesday, June 29, 2004
|
Does anyone know how to create a trigger to send email notification to a mailing list when the contents of a folder change???
|
|
|
Posted by :
Maharg at 11:06 on Friday, August 27, 2004
|
Wonder if you can help. I have written a pocedure to delete rows in a table. The rows are deleted by clicking a delete button beside each row. How can I tie each row to a certain delete button.
|
|
|
Posted by :
hamada at 07:34 on Wednesday, December 08, 2004
|
how to create table with trriger
i.e if i made table and when i insert a new item in the table how can creat another table automatically with a new details
|
|
|
Posted by :
Usarian at 09:22 on Thursday, March 03, 2005
|
This is an EXCELLENT intro into triggers. I have used it, and am now using this technique repeatedly. Follow the instructions to the letter and you'll be fine!
|
|
|
Posted by :
mreddy at 14:00 on Tuesday, March 15, 2005
|
Hi,
Anyone repling to these questions... cuz i don't see any answers to the questions. If anyone know the answer to this post please reply me as well.
[u]Posted by : Archive Import (Rebekah) at 19:13 on Sunday, May 11, 2003 [/u]
I really need help with an Update trigger that puts the current date in a changed record. I have used regular UPDATE, and also IF (COLUMNS_UPDATED()). Whenever I run either trigger, all records in the table are affected. I won't be able to identify the record being changed other than that it'll be the current record. Is there a way to write that in?
Thank you,
mansi
|
|
|
Posted by :
pinky34 at 02:49 on Saturday, May 28, 2005
|
Hai
I need to know how to trigger mail to a user whenever a change is made in the table.
can you give some tips?
Regards
kumar
<Added>
Hai
I need to know how to trigger mail to a user whenever a change is made in the table.
can you give some tips?
Regards
Pinky
<Added>
Hai
I need to know how to trigger mail to a user based on select statement in the table,after every 5 minutes.
can you give some tips?It is urgent
Regards
Pinky
|
|
|
Posted by :
shahbaz at 06:17 on Tuesday, July 05, 2005
|
Well it was really a very useful information for me...as i was told to write the trigger for as application...hope i'll get all kind of information from the site.
thanx
Shahbaz
<Added>
Well it was really a very useful information for me...as i was told to write the trigger for an application...hope i'll get all kind of information from the site.
thanx
Shahbaz
|
|
|
Posted by :
askia1500 at 08:42 on Tuesday, May 09, 2006
|
when an employee resigns, the resignation date is updated in the employee table. After the resignation date is updated, the icurrentstrength attribute of the position table shoul be decreased by 1
|
|
|
To post comments you need to become a member. If you are already a member, please log in .
 
| RELATED ARTICLES |
ASP Format Date and Time Script
by Jeff Anderson
An ASP script showing the variety of date and time formats possible using the FormatDateTime Function. |
 |
Creating a Dynamic Reports using ASP and Excel
by Jeff Anderson
A simple way to generate Excel reports from a database using Excel. |
|
Create an ASP SQL Stored Procedure
by Jeff Anderson
A beginners guide to setting up a stored procedure in SQL server and calling it from an ASP page. |
|
ASP Shopping Cart
by CodeToad Plus!
Complete source code and demo database(Access, though SQL compatible) to an ASP database driven e-commerce shopping basket, taking the user through from product selection to checkout. Available to CodeToad Plus! Members |
|
Email validation using Regular Expression
by Jeff Anderson
Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP. |
|
Creating an SQL Trigger
by Jeff Anderson
A beginners guide to creating a Trigger in SQL Server |
|
The asp:checkbox and asp:checkboxlist control
by David Sussman, et al
Checkboxes are similar to radio buttons, and in HTML, they were used to allow multiple choices from a group of buttons. |
|
ASP.NET Forum Source Code
by ITCN
Complete open source website Forum and Discussion Board programmed in Microsoft dot Net 1.1 Framework with Visual Basic. |
|
The asp:listbox control
by David Sussman, et al
The next HTML server control that we'll look at, <asp:listbox>, is very much related to <asp:dropdownlist>. |
|
Concatenate strings in sql
by Jeff Anderson
A brief introduction to concatenating strings in an sql query (using SQL server or access databases). |
|
|
|
