If I update a record such as
Update MyTable SET MyField = 'MyValue'
where MyKey = 'Key1'
The trigger fires .....
If I update multiple records
Update MyTable SET MyField = 'MyValue'
where MyKey IN ('Key1','Key2')
Does the trigger (Update) on MyTable fire more than once or once.
hi,
I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.
a
win server 2003 standard Edition
sql Server 2000
db:aaa
table: a_aaa
column:a_aaa_a
b
win server 2003 standard Edition
sql Server 2000
db:bbb
table:b_bbb
column: b_bbb_b
It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera......
Can anyone help?
Thanks in advance.
CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EndDate as DATE
DECLARE @CompCode as VARCHAR(15)
DECLARE @PhoneGMSM as VARCHAR(10)
DECLARE @PhoneALERT as VARCHAR(10)
SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA;
SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111"
WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
WHERE:
USER_DATA_DEVIQ = b_bbb_b
AUSADFORMULA02 = bbb
QDB_KS = b_bbb
USER_DATA = a_aaa_a
AUSSMEDEVIQ1 = aaa
QDB_Test = a_aaa
END
GO
I have an update trigger that works wonderful as long as I am updating the row in Enterprise Manager, but if I update the same column and row using an update statement in Query Analyzer the trigger doesn't fire.
Any ideas why?
TIA.
Dear Experts,I'm an Oracle guy, who is being given more SQL Server assignmentslately.I've been looking for things on the web about this, but I can'tanything so far.In Oracle, I you can create a trigger on a table that -only- fires ifcertain fields are updated.create or replace trigger trg_some_triggerBEFORE insertOF some_field1, some_field2on tbl_some_tablefor each row....Is this also possible in SQL Server?What is the syntax please?Thanks a lot!
View 2 Replies View RelatedHi
In SQL Server 2005, does TRIGGER for DML fire per statement or per Record ?
If both, can we define the way it should fires.
thanks / ramanuj
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
In Pablo Castro webcast, First Look at ADO.NET 2.0, he mentions the use of UpdateBatchSize which I think would be handy.However, I was not able to get it to work.
Dim t As New Global.System.Data.SqlClient.SqlDataAdapter("Select * from Table_1", Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)Dim d As New Global.System.Data.DataTablet.FillSchema(d, SchemaType.Source)Dim cb As New Global.System.Data.SqlClient.SqlCommandBuilder(t)For c As Int32 = 1 To 10000Dim r As Global.System.Data.DataRow = d.NewRowr("id") = Global.System.Guid.NewGuidr("val") = CType(Rnd(), Int32)d.Rows.Add(r)Nextt.UpdateBatchSize = 100t.Update(d)d.Dispose()t.Dispose()
What ends up showing in Sql Server Profiler (Sql Server 2005) is each Insert being executed in it's own statement:exec sp_executesql N'INSERT INTO [Table_1] ([id], [val]) VALUES (@p1, @p2)',N'@p1 uniqueidentifier,@p2 int',@p1='3793CB5E-3B7A-45E7-9A53-0BD528BB6B07',@p2=1
I think I made this example very simple and yet can't fathom why it won't batch the statements.
I'm somewhat aware of SqlBulkCopy and was very pleased with that speed, but don't think it would handle the Update,Delete,Insert that the SqlDataAdapter.Update would.
Originally started using xsd DataSets until I saw the data tutorials in the Learning section here in which case I copied out the autogenerated TableAdapter classes and fuddling with them to do what I want since batching was not something I saw in TableAdapters.
Does anyone see what I'm missing here?Nathan
I have "sqlGetReservationMembers" datasource in my ascx code and another datasource in 'KillReservation'. As you can see "sqlGetReservationMembers" uses a SELECT command only. I have verified this several times. The 3rd block below is the only place in code-behind where "sqlGetReservationMembers" is referenced. When the "KillReservation" fires I get an error stating "Deleting is not supported by data source 'sqlGetReservationMembers' unless DeleteCommand is specified". I'm confused because I can't see where the 'sqlGetReservationMembers' delete event is being fired. I have removed the datasource and recreated it but the problems returns. What am I missing?
Thanks
<asp:SqlDataSource ID="sqlGetReservationMembers" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>" SelectCommand="sp_GetReservations" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="gvMyRides" Name="ID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>--------------------------------------------------------------------------------- Protected Sub KillReservation(ByVal RideID As Integer, ByVal MemberID As Integer)
Using Myconnection As New SqlConnection(Config.GetConnectionString("SiteSqlServer")) Dim Mycommand As New SqlCommand("sp_KillReservation", Myconnection) Mycommand.CommandType = CommandType.StoredProcedure Mycommand.Connection.Open() Mycommand.Parameters.Add(New Parameter("@MemberID", TypeCode.Int32, MemberID)) Mycommand.Parameters.Add(New Parameter("@RideID", TypeCode.Int32, RideID)) Mycommand.ExecuteNonQuery() Myconnection.Close() End Using
End Sub----------------------------------------------------------------------------------Protected Sub gvMyRides_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvMyRides.SelectedIndexChanged
sqlGetReservationMembers.SelectParameters.Item("ID").DefaultValue = Me.gvMyRides.SelectedValue Me.gvReservationList.DataSourceID = Me.sqlGetReservationMembers.ID
..More Code..........
End Sub-----------------------------------------------------------------------------------
I am using the SqlDependency to notify me of data changes in a table. However, as soon as I start it, the OnChange event fires over and over even though no data has changed.
Essentially All I want to do is be notified when records are inserted into a table. I am able to get another example to work using a Service Broker Queue and a sql query of WAITFOR ( RECEIVE CONVERT(int, message_body) AS msg FROM QueueMailReceiveQueue ) However I would prefer not to use a queue for once my messages have been read they are taken off the queue and I would rather control that manually.
Any help with getting SqlDependency to notify my app when records are added and not over and over when the data has changed would be great.
Here is my code:
Code Snippet
public partial class Form1 : Form {
public static event OnChangeEventHandler OnChange;
string _strConnString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=email_queue;Pooling=False;";
string _strSql = "SELECT email_id from email where isprocessed = 0";
private DataSet dataToWatch = null;
private SqlConnection connection = null;
private SqlCommand command = null;
SqlDependency dependency = null;
SqlDataReader sdr = null;
public Form1() {
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e) {
SqlDependency.Stop(_strConnString);
SqlDependency.Start(_strConnString);
if (connection == null) {
connection = new SqlConnection(_strConnString);
connection.Open();
}
if (command == null) {
command = new SqlCommand(_strSql, connection);
}
if (dataToWatch == null) {
dataToWatch = new DataSet();
}
GetData();
}
private void GetData() {
dataToWatch.Clear();
command.Notification = null;
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
command.CommandTimeout = 400;
using (SqlDataAdapter adapter = new SqlDataAdapter(command)) {
adapter.Fill(dataToWatch, "email");
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e) {
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired) {
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
this.Text = DateTime.Now.ToString();
GetData();
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e) {
SqlDependency.Stop(_strConnString);
if (connection != null) {
connection.Close();
}
}
}
Hi!
I have written some code to insert a record into the table in BtnAdd click event and I also have a Grid view control to show the table records.
If I click the Add button ,the record gets inserted into the table and shown in the grid correspondingly.But if I refresh teh page,the same data gets inserted again since it fires the Btnclick Event.
Please help me out in this issue.
Thanks
Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
View 2 Replies View RelatedHey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedThis Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies View RelatedWhen a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.
View 4 Replies View RelatedSalve, non riesco a disabilitare un trigger su sqlserver nè da queryanalyzer, nè da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.
View 4 Replies View RelatedHi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?
thanks in advance
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER RemoveTriggers
ON tblTriggers
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @DeleteTrigger as nvarchar(max)
select @DeleteTrigger = TableName FROM DELETED
IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL
DROP TRIGGER @DeleteTrigger
GO
END
GO
Hi,
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.
There are two different Stored Procedures insert data into a table, is it possible to catch what stored procedure name was that trigged the trigger in the trigger?
View 1 Replies View RelatedHi, I am trying to script a trigger on a table to accomplish the following... If a row is updated then the value in the 'date' column in that specific row would update to getDate().
I have never created a trigger before and finding it very difficult. Thank you very much in advance.
Kind Regards,
Sean.
Hello
I am having difficulty try to get a trigger to work.
I need to create a trigger querie that will insert a row from TableMain (Column LNames) table to TableA (Column LNames)
Below is my querie
CREATE TRIGGER [trig_addTable_A]ON Table_A FOR INSERT ASINSERT LName (LName)
SELECT LNameFROM TableMain inserted
I have inserted test names into the main table but nothing in table A has inserted. What have I done wrong.
Thanks
I am looking for a INSERT trigger example, to achieve the following objective:
I would like the trigger to be fired when a row gets inserted into a table. I want to retrive some value from the inserted row and use this value to insert a new row in table 2.
Thanks
chri
is it possible to run a trigger forever... whose work is to archive data from a table. or how can i do the operation to archive automatically ?Regards,Kamrul Hassan
View 1 Replies View RelatedHi,
Does anybody know how could I define a for each row trigger in sql Server 2005?
What I need to do is before inserting in the table look through the rows to check wheter that value overlaps with the others.
"Overlap" means that this value cannot be betweent the values fo two different columns, that is why I need to go through all the table.
In case that was not possible, I propose the following, load the table in a dataset and check the constraint with a for loop despite of the performance could be decreased.
Any suggestion?.
Thanks in advance.
Cheers.
Hi everybody
I work on a Project that has Inbox .
I need that if a record has inserted in inbox system alert to user that view it's inbox .
The suitable solution for it seems trigger ,but i don't how trigger could communicate with Application.
Thanks for any guides.
Best Regard
A.Doroudian
hi .... I am going to use trigger first time.
my secnerio is that I want a common column in five tables on the insertion of a row in a particular table..
question is simple ..... can I do that????????????????????
thanks in advance.
Hello Experts,
Currently I have 3 tables; Persons, Student and Teacher. Persons is the parent table of Student and Teacher and they are linked by the Person_Id attribute. For example, if Person_Id 1 exist in Student table, it cannot be inserted anymore into Teacher table. How do I write a trigger in MS SQL to implement this business rule? Any experts can shed some light? Thanks!
I am trying to write a trigger that alows me to re-arrange the order of items. For instance, if I insert a record with a SF_NEWS_Order of "1" then all records with an order of "1" or more would increase by 1. This allows me to insert a record in the list of priorities. For some reason, the trigger is not working. If I have reords with SF_NEWS_Order values as follows:
1
2
3
I end up with this:
1
1
2
3
Can someone help with this? The code of the existing trigger is below:
CREATE TRIGGER NEWS_PRIORITY ON [dbo].[TBL_SF_NEWS]
FOR INSERT, UPDATE
AS
Declare @Order_Code int
Declare @New_ID int
Declare @Exist_Record int
Set @New_ID = ( Select SF_NEWS_ID from Inserted )
Set @Order_Code = ( Select SF_NEWS_Order from Inserted )
Set @Exist_Record = ( Select SF_NEWS_ID from [dbo].[TBL_SF_NEWS] Where SF_NEWS_Order = @Order_Code and SF_NEWS_ID <> @New_ID )
If (@Exist_Record <> Null ) Begin
Update [dbo].[TBL_SF_News] Set SF_NEWS_Order = (@Order_Code + 1) Where SF_NEWS_ID = @Exist_Record
End