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?
I created a trigger that will throw a message whenever a new record is inserted in the table. Now I want to remove this trigger. I am not able to remove.
CREATE TRIGGER prod_culture_trig ON Production.Culture AFTER INSERT AS SELECT 'New culture entry added';
I get the following error message:
Msg 2714, Level 16, State 2, Procedure prod_oulture_trig, Line 4 There is already an object named 'prod_oulture_trig' in the database.
This error confirms that we have the trigger already existing. Now I run the code
DROP TRIGGER prod_oulture_trig;
I get the following error message - Msg 3701, Level 11, State 5, Line 1 Cannot drop the trigger 'prod_culture_trig', because it does not exist or you do not have permission.
What permission do I require? This is a test database on my computer with me as the administrator.
I have a DDL Trigger that writes a record into a table when any DDL event happens.
It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)
I created a trigger on a replicated table in a publishing database on SQL Server 2000. When I attempt to ALTER TABLE ... DISABLE TRIGGER ..., I get a message that I cannot alter the table since it is part of a publication. Does anyone know if I would be able to issue a DROP TRIGGER or ALTER TRIGGER on a replicated table?
I am writing an audit trail trigger that needs to query columns, but the specific column names are not known until the trigger runs and determines what columns have changed. Once I know the changed column(s) I fetch the column name. No sweat. The problem is using a variable in place of a column name I can not get the data to return, only the column name.
-- Here is how I get the column name. It works great, @CName -- reflects the column name as I use it elsewhere fine. Select @CName = col_name(OBJECT_ID('TABContract'), @CNum)
-- This simple syntax usually works, it does not using a -- variable as the column name. @Prev is populated with -- @CName literally. I have tried various and asundry ()'s and []'s Select @Prev = @CName From Deleted
-- Here I have hard coded the Column name and ran a test modifying that column "Comment" and it works! @Prev is populated with the columns data not the Column name. Select @Prev = Comment From Deleted
-- Tried these syntaxes, no luck Select @Prev = (Select @CName From Deleted) Select @Prev = (Select col_name(OBJECT_ID('TABContract'), @CNum) From Deleted)
Hello, I have a very simple trigger that only copies the data from one table to a historial table, when a row is deleted. It works fine, but now I need to change it becouse I am inserting a new column to the table, which type is ntext, and when I try to declare a ntext variable (inside the trigger), it does not allow me. Does anyone know how to go round this? Here is the code of the trigger (maybe it is not efficient, as I am a newbie in this :D, please tell if so as well)
CREATE TRIGGER OnDelete ON dbo.Tracker FOR DELETE AS
declare @IDregistro as integer; declare @IDTIT as integer; declare @negocio as nvarchar(50); declare @fechaOr as datetime; declare @asuntoOr as nvarchar(50); declare @estadoOr as nvarchar(50); declare @descripcionOr as nvarchar(255); declare @usuarioOr as nvarchar(50); declare @contactoOr as nvarchar(50); declare @companiaOr as nvarchar(40); --declare @desc as ntext; --DOES NOT WORK!!
declare @fechaSus as datetime
select @fechaSus = getdate()
select @IDregistro = IDregistro from deleted; select @IDTIT = IDTitular from deleted; select @negocio = negocio from deleted; select @fechaOr = fecha from deleted; select @asuntoOr = asunto from deleted; select @estadoOr =estado from deleted; select @descripcionOr = descripcion from deleted; select @usuarioOr = usuario from deleted; select @contactoOr = contacto from deleted; select @companiaOr = compania from deleted;
I need to pass a application userid on every call to my database for triggers on the db to use.
I am connecting to my database using integrated security from my application, all on one windows domain account. However I need to get an application user id over on the connection for triggers to use to stamp in changelogs.
Currently everytime we open a connection we create a #table with the user id in it, but that is expensive, especially in our new SOA where connections are frequently created and dropped on requests.
Is there any way I can get a connection level variable over to the database server so triggers could read it?
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.
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'
This 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.
__________________________________________________ _ CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS BEGIN DECLARE @v_object_key VARCHAR(80) DECLARE @v_object_name VARCHAR(40) DECLARE @v_object_verb VARCHAR(40) DECLARE @v_datetime DATETIME
SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!! SET @v_object_name = 'FochiniTable' SET @v_object_verb = 'Create' SET @v_datetime = GETDATE()
IF ( USER <> 'webuser' ) INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment) VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')
END ________________________________________________
i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id
and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 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.
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)
When 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.
Salve, 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.
Hi, 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,
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
I need to run the alter table to drop a default. However, the default name is kind of 'dynamic' from around 1000 databases, thus I need to run the following sql to get the name to a variable. Now, it looks the alter table statement does not like to drop a vaiable, is there a solution about it?
declare @radius_default varchar(40) select @radius_default = (select sobj.name from sysobjects sobj inner join syscolumns scolumn on sobj.ID = scolumn.cdefault where scolumn.name = 'radius' and sobj.name like '%LandMarks%') print 'Need To Drop @radius_default: ' + @radius_default --==================================== alter table LandMarks drop constraint @radius_default
I coulnd't Able to see Drop Down Menu of Any Variables in Variable Name of RowCount Task
Flow:
Aggregation Task--------> RowCount Task------------------->Flat FileConnection Task | | In this Task Variable Name Column Doesn't Show Me Drop Down List of Available variables.
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?
Hi, 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
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
Hi, 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?.
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.