hi!
This trigger i made does not execute..........can anybody help?
"Create TRIGGER update_status
on book_issue
for insert
as
update book_catalogue
SET status='I' where book_catalogue.code = book_issue.code;
go"
I basically want the status of the book to change to 'I' in the book_catalogue whenever an entry is made in the book_issue.
tables:
book_catalogue
book_issue
P.S the problem realy is in the last sentence i.e, .........where book_catalogue.code=book_issue.code
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
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.
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)
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable] AFTER UPDATE AS SET XACT_ABORT ON; SET NOCOUNT ON; IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer] ON ALL SERVER WITH EXECUTE AS 'Smith' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Smith' begin if exists (SELECT 1 FROM sys.dm_exec_sessions WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') ) ROLLBACK; end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
In my project I have three table 2 are Master detail type and other is parameter table.tablleA, tableB, tableC. I want to create a trigger at the time of inserting data in table a, I want to Insert records in table B according to Primary keyy of tableA and Data using from tableC also.
I created a Insert trigger which is attached to a table(say tblUser) on SQL Server. After the trigger is fired, the newly inserted data is in the virtual table(Insert) which was created by the trigger. What I like to do is to modify the data in the virtual table(Insert), then put the changed data into the table(tblUser). Is there any way to do that?
I want to create a trigger in ms sql server 2005.What I want it to do:I create a new user in the aspnet_users table. When this user is inserted I want to automatically insert, username, userid and createdate in the tblUserData table.Can anyone provide me with a clear example on how to do this?Thanks!
I'm making an app with VB6.0 and SQL Server 6.5, and I have this problem:
I have a table called “comerciales” in which the field “comercial” is primary key. I have another table called “clientes” in which the field “comercial” is foreign key to table “comerciales”.
I want to make a trigger that update to NULL the field “comercial” of table “clientes” when I delete the same value of field “comercial” in table “comerciales”. That is, if I delete the comercial “Jhon Winter” in table “comerciales” I want that the records of table “clientes” with comercial = "Jhon Winter" update that field to NULL
I have this trigger, but it gives an error when I execute. Error: “error near delete”
CREATE TRIGGER trg_comerciales ON dbo.comerciales FOR DELETE
BEGIN UPDATE clientes SET clientes.comercial = NULL FROM deleted WHERE clientes.comercial = deleted.comercial END
What is wrong?. Any idea?. Can anybody help me?. Another way of doing the same? I don't know much about triggers any advice about them, will bw greatly apreciatted.
I am trying to bypass processing in an INSERT SQL Server Trigger when a row is added into a table by a specific stored procedure. Is there anyway before hand in SQL Server to skip the processing inside a trigger whenever it is called by a specific process or is there a way to get the stored procedure id that is inserting a row in the triggering table before processing anything.
i want to be able to trigger a sp on another server from within an sp. I dont want to do this via a linked server as this will hold up the job while it waits for a response to say the sp has completed. Is there another way i can do this? i have a number of them and i did think of setting up a dts on the other server for each one of them and then executing the dts packages instead.
In my Projecti want to check the date at the time of insert in A-Tablethat it should be Greater than (>) Date Defined in B-TableNote:-B-table have only one record so plz tell me how can i do using Sql-Server Backend only
I am using a lot of triggers to link couple of systems.Is there a way to administrate triggers? Is there a tool I can install to do that?I need to be able to list all the triggers in the database, search them, enable/disable. Thanks,Igor
I'm trying to insert a record into a table and I'm finding that I can only insert the primary key. When I try to insert only another field only it does not happen which makes me think the primary key is not being auto-created and I think that I have to set up a trigger to do thisExample:<CODE> oConn.ExecuteMySQL("INSERT INTO tblUsers(strUserID,strName)VALUES(45,'x')", True)</CODE>.The code above works well, but I want to auto-generate a trigger that will insert a unique strUserID (primary Key) each time a user does an insert so that I dont have to specifically indicate strUserID.<CODE> oConn.ExecuteMySQL("INSERT INTO tblUsers(strName)VALUES('x')", True)</CODE>The code above does not work well because it does not create a strUserID. How do I create a trigger that will auto-insert a primary key into strUserID? Thanks.Joe
How can we elegantly tell a trigger to deal with a single row or mutiple rows? Update/delete may be a single row or multiple row operation. Please provide T-SQL code sample. Thanks.
CREATE TRIGGERReportRequestINSERT_trg ONReportRequest_tbl FOR INSERTAS
Can an insert trigger like this successfully insert into a linked server's table? Replication is NOT an option. And I only need to do it in very limited circumstances anyway.
INSERT INTO [172.22.202.180].MultiVendor.dbo.ReportRequest_tbl ( ControlCustomerNumber, UserID, ReportName, RequestedDate ) SELECT ins.ControlCustomerNumber, ins.UserID, ins.ReportName, ins.RequestedDate FROM Inserted ins
create or replace trigger trgRNR_Budget before insert on tblRNR_Budgets for each row when (new.BUDGETID is null) begin select RNR_BID.nextval into :new.BUDGETID from dual; end; /
How to write above before insert trigger for each row in sql server...
CREATE TRIGGER User_Insert ON aspnet_users AFTER UPDATE AS IF UPDATE (UserId) insert into users (aspnet_user_id, username) select au.userid, au.username from aspnet_users au where au.userid != users.aspnet_user_id;
Whenever the aspnet_users table has a new user added I want the Userid and username fields from that table inserted into the the aspnet_user_id and username fileds of the users table.
when creating the trigger I get the following error..
Msg 4104, Level 16, State 1, Procedure User_Insert, Line 3 The multi-part identifier "users.aspnet_user_id" could not be bound.
Have gone through BOL and Google, but can't find the answer... please helpwith a simple Q. I'm trying to create a simple cascade delete trigger in SQLServer 7 where deleting "parent" records in table X delete correspondingchild records in table Y.Table X=========X_IDSOME_VALTable Y=========Y_IDX_IDSOME_VALWhen there is no relationship between X.X_ID and Y.X_ID, the followingtrigger works fine:CREATE TRIGGER "temp" ON xFOR DELETEASdeletefrom ywhere x_id in (select x_id from deleted)However, when a relationship is created to enforce referential integrity,the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCEconstraint" error. I've seen examples where the trigger says (for example)"AFTER INSERT", where presumably the code is specifically run after theevent that triggers it -- is there a way of forcing the trigger to runbefore the delete from table X is executed? I've tried using "BEFOREDELETE", but no dice :-Thanks!--Aidan Whitehall <aidanwhitehall@fairbanks.co.uk>Macromedia ColdFusion DeveloperFairbanks Environmental Ltd +44 (0)1695 51775
Hi,can someone tell how to write a Trigger; I am familiar with Sybase SqlAnywhere trigger syntax.Actually I have three tables MEMBER, CONTRACT and PAYMENTI need to update the MEMBER.BALANCE once the PAYMENT.AMOUNT is INSERTEDwhere PAYEMENT.CONTRAC_ID = CONTRACT.CONTRAC_IDand CONTRAT.MEMBER_ID = MEMBRE.MEMBER_IDI have more TRIGGERS to write, but with a good example it would be greatIn fact, send me as many examples as you canThanksFernandJoin Bytes!
I'm having problems with a CLR Trigger trying to get it to work with SQL Server... and i believe it is because permissions... basicly i want to make my trigger get the name of a newly created database and then look for a table and then see if is empty... then add some rows... thats all...
I have set my databases as Trustworthy and also i have enabled the "clr enabled" parameter... i mark my project as "Safe" in the Project properties... and it still doesn't work... do i need to sign my project..? if so.. how?
i'm clueless...
Thank You.!
Code Block 'Table_1' table - Unable to create table. A .NET Framework error occurred during execution of user-defined routine or aggregate "Trigger1": System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host. The protected resources (only available with full trust) were: All The demanded resources were: UI System.Security.HostProtectionException: at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException) at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action) at Triggers.Trigger1() . The statement has been terminated.
I have a trigger written below to update another table when one table is updated. Basically it works when i update row after row from enterprise manager However when i use a script to update 10 or 20 rows then this trigger does not work. Can any one offer any suggestions on where this issue could be ?
DECLARE @iCount INT DECLARE @OldValue VARCHAR(255) DECLARE @NEWVALUE VARCHAR(255) DECLARE @EQNAME VARCHAR(255) DECLARE @EQTYPE VARCHAR(255) DECLARE @TEST VARCHAR(255) SET @NEWVALUE = 'INIT' IF (UPDATE(VALUE)) BEGIN SELECT @OldValue = deleted.value from deleted SELECT @NEWVALUE = inserted.VALUE,@EQNAME = inserted.EQ_NAME FROM inserted where inserted.attr = 'AreaCfg' IF (@@ROWCOUNT =0) BEGIN RETURN END IF ((@NEWVALUE = '') OR (@NEWVALUE is NULL)) BEGIN DELETE [TABLE-1] WHERE ENTITYID = @EQNAME RETURN END IF ((@NEWVALUE != @OldValue)) BEGIN SELECT @iCount = Count(*) FROM [TABLE-1] WHERE ENTITYID = @EQNAME IF (@iCount = 0) BEGIN -- No records are available so insert SELECT @EQTYPE = Typ FROM IP_EQ WHERE EQ_NAME = @EQNAME INSERT INTO [TABLE-1] (AlarmGroupId, EntityID, EntityType, OwnerId) VALUES (@NEWVALUE,@EQNAME,@EQTYPE,'Production Tracker') END IF (@iCount > 0) BEGIN -- No records are available so insert SELECT @EQTYPE = Typ FROM IP_EQ WHERE EQ_NAME = @EQNAME UPDATE [TABLE-1] SET ALARMGROUPID = @NEWVALUE WHERE ENTITYID = @EQNAME END END END
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.
