Trigger Use
I have a Sequel Server 2005 database with a Table called Main Call Issue. I want to update the DateTimeModifed field to be the Current System date without changing my original DateTimeAdded field when the record was added to the database. I know a Trigger will work here. Where do I place the Trigger coding? I have the SQL Server Management Studio on my computer. Do I create the Trigger here under my database just like stored procedures are created. How does the trigger run when an update is made to a combo box. I have four combo box fields with Lists that can be updated. Whwn I update the Issue Combo Box inserting a different list name, I want the record updated and the DateTimeModified field to show the date I changed the record.
Private Sub MainCallIssueBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MainCallIssueBindingNavigatorSaveItem.Click 'If Me.IDTextBox.Text <= Position Then MessageBox.Show("Record saved.") Me.DateTimeAddedTextBox.Text = Date.Now() Me.DateTimeModifiedTextBox.Text = Date.Now() Me.TechnicianTextBox.Text = "G.Casto" Me.UserAddedTextBox.Text = Me.TechnicianTextBox.Text Me.UserModifiedTextBox.Text = Me.TechnicianTextBox.Text Me.Validate() Me.MainCallIssueBindingSource.EndEdit() Me.MainCallIssueTableAdapter.Update(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue) Me.MainCallIssueTableAdapter.FillBy(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text) Exit Sub MessageBox.Show("Record updated.") Me.DateTimeModifiedTextBox.Text = Date.Now() Me.UserModifiedTextBox.Text = Me.TechnicianTextBox.Text Me.Validate() Me.MainCallIssueBindingSource.EndEdit() Me.MainCallIssueTableAdapter.Update(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue) Me.MainCallIssueTableAdapter.FillBy(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text) Exit Sub End Sub Private Sub TechCallIssue_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Time' table. You can move, or remove it, as needed. Me.TimeTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Time) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.ResolutionTime' table. You can move, or remove it, as needed. Me.ResolutionTimeTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.ResolutionTime) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Resolution' table. You can move, or remove it, as needed. Me.ResolutionTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Resolution) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Issue' table. You can move, or remove it, as needed. Me.IssueTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Issue) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.MainCallIssue' table. You can move, or remove it, as needed. Me.MainCallIssueTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue) Me.MainCallIssueTableAdapter.FillBy1(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text) 'Me.MainCallIssueBindingSource.AddNew() Me.MainCallIssueBindingSource.MoveLast() NewPosition = Me.MainCallIssueBindingSource.Position + 1 Position = Me.MainCallIssueBindingSource.Position SelectedPosition = Me.MainCallIssueBindingSource.Position + 1 IsDirty = False End Sub
Software/Hardware used:
Visual Studio 2005
Private Sub MainCallIssueBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MainCallIssueBindingNavigatorSaveItem.Click 'If Me.IDTextBox.Text <= Position Then MessageBox.Show("Record saved.") Me.DateTimeAddedTextBox.Text = Date.Now() Me.DateTimeModifiedTextBox.Text = Date.Now() Me.TechnicianTextBox.Text = "G.Casto" Me.UserAddedTextBox.Text = Me.TechnicianTextBox.Text Me.UserModifiedTextBox.Text = Me.TechnicianTextBox.Text Me.Validate() Me.MainCallIssueBindingSource.EndEdit() Me.MainCallIssueTableAdapter.Update(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue) Me.MainCallIssueTableAdapter.FillBy(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text) Exit Sub MessageBox.Show("Record updated.") Me.DateTimeModifiedTextBox.Text = Date.Now() Me.UserModifiedTextBox.Text = Me.TechnicianTextBox.Text Me.Validate() Me.MainCallIssueBindingSource.EndEdit() Me.MainCallIssueTableAdapter.Update(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue) Me.MainCallIssueTableAdapter.FillBy(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text) Exit Sub End Sub Private Sub TechCallIssue_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Time' table. You can move, or remove it, as needed. Me.TimeTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Time) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.ResolutionTime' table. You can move, or remove it, as needed. Me.ResolutionTimeTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.ResolutionTime) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Resolution' table. You can move, or remove it, as needed. Me.ResolutionTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Resolution) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Issue' table. You can move, or remove it, as needed. Me.IssueTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Issue) 'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.MainCallIssue' table. You can move, or remove it, as needed. Me.MainCallIssueTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue) Me.MainCallIssueTableAdapter.FillBy1(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text) 'Me.MainCallIssueBindingSource.AddNew() Me.MainCallIssueBindingSource.MoveLast() NewPosition = Me.MainCallIssueBindingSource.Position + 1 Position = Me.MainCallIssueBindingSource.Position SelectedPosition = Me.MainCallIssueBindingSource.Position + 1 IsDirty = False End Sub
Software/Hardware used:
Visual Studio 2005
ASKED:
September 15, 2009 6:18 PM
UPDATED:
September 16, 2009 8:10 PM
Answer Wiki:
Database triggers don't respond to application events.
You could create a trigger that will fire when an UPDATE is performed on a specific table, but you would have to make your application update that table when the combo box is modified.
You can create triggers from Management Studio by expanding the desired table tree, right-clicking on "Triggers" and selecting "New Trigger", or with the <a href="http://doc.ddart.net/mssql/sql70/create_8.htm">CREATE TRIGGER command</a>.
-------------
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
