I have two table with the same structure (ID, DATE_1, DATE_2).... PLANS and AUD_PLANS. I need to create a trigger (FOR INSERT) that do the following things:
In PLAN table i have the next row: (1234, '01/01/2006', '30/01/2006'). I need to capture these values to compare with the row to insert. How can i do this?
I try this into de Trigger Code ....
DECLARE @FECHA_1_ANT AS datetime
DECLARE @FECHA_2_ANT AS datetime
DECLARE @FECHA_1_INS AS datetime
DECLARE @FECHA_2_INS AS datetime
Select @FECHA_1_ANT = P.DATE_1, @FECHA_2_ANT =P.DATE_2 from PLANS P inner join INSERTED I on P.ID = I.ID
But the values for @FECHA_1_ANT and @FECHA_2_ANT are the same that the values to insert.....
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 one trigger for Auditing purpose.Currently we are using SQL Server 2005. I have gone thru some articles on DDL trigggers but iam not able to get good info.
Frns, Need a trigger which can track all ddl's on all tables in the current database in an audit table. It should include dropping tables,altering tables ... I should'nt allow both these activities. The action performed must be tracked in the following table.
create table audit_ddl ( login_user varchar(20), login_time datetime, table_name varchar(50), action_performed varchar(200) -- Entire ALTER/DROP SQL stmt the user issued issued must be tracked here. )
I have a database replicated to another database in a remote server. We have a trigger in one of the tables in remote Database(replicated), which will fire as soon as there is an insert into this table. This trigger is maintaining the business logic. This trigger will insert the records into another table depending on what kind of information obtained from the new row(which was replicated). Problem is, since we are using replication ( it uses bcp internaly), the trigger is not firing. I some how wanted to add these new records to the other table (automated). Can you guys please advice me on how to implement this. I appreciate your help Thanks, Jeyam
There are two table table A and table B , if something is updtaed in table A , the same should reflect in table B, i wrote a trigger upwhen i modify any thing table A it does not reflect in table B could any one guide me through this....
Here is the Update trigger i wrote :
CREATE TRIGGER [updbacklog] ON [testbacklog] FOR UPDATE AS Update test1backlog Set test1backlog.WorkOrderNumber = inserted.WorkOrderNumber , test1backlog.SalesOrderNumber = inserted.SalesOrderNumber , test1backlog.CustPONumber = inserted.CustPONumber , test1backlog.Status = inserted.Status , test1backlog.Comments = inserted.Comments , test1backlog.TargetCompletionDate = inserted.TargetCompletionDate , test1backlog.ActualCompletionDate = inserted.ActualCompletionDate , test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired , test1backlog.WorkOrderType = inserted.WorkOrderType , test1backlog.CustomerName = inserted.CustomerName , test1backlog.Attn = inserted.Attn , test1backlog.CustAddr1 = inserted.CustAddr1 , test1backlog.CustAddr2 = inserted.CustAddr2 , test1backlog.CustAddr3 = inserted.CustAddr3 , test1backlog.City = inserted.City , test1backlog.State = inserted.State , test1backlog.Postal = inserted.Postal , test1backlog.Customer = inserted.Customer , test1backlog.Address = inserted.Address , test1backlog.ShipDate = inserted.ShipDate , test1backlog.Carrier = inserted.Carrier , test1backlog.Waybill = inserted.Waybill , test1backlog.CanBeShipped = inserted.CanBeShipped , test1backlog.PlannerCode = inserted.PlannerCode , test1backlog.rowguid = inserted.rowguid from inserted join test1backlog on inserted.WorkOrderNumber = test1backlog.WorkOrderNumber and test1backlog.SalesOrderNumber = inserted.SalesOrderNumber and test1backlog.CustPONumber = inserted.CustPONumber and test1backlog.Status = inserted.Status and test1backlog.Comments = inserted.Comments and test1backlog.TargetCompletionDate = inserted.TargetCompletionDate and test1backlog.ActualCompletionDate = inserted.ActualCompletionDate and test1backlog.IsCommercialReportRequired = inserted.IsCommercialReportRequired and test1backlog.WorkOrderType = inserted.WorkOrderType and test1backlog.CustomerName = inserted.CustomerName and test1backlog.Attn = inserted.Attn and test1backlog.CustAddr1 = inserted.CustAddr1 and test1backlog.CustAddr2 = inserted.CustAddr2 and test1backlog.CustAddr3 = inserted.CustAddr3 and test1backlog.City = inserted.City and test1backlog.State = inserted.State and test1backlog.Postal = inserted.Postal and test1backlog.Customer = inserted.Customer and test1backlog.Address = inserted.Address and test1backlog.ShipDate = inserted.ShipDate and test1backlog.Carrier = inserted.Carrier and test1backlog.Waybill = inserted.Waybill and test1backlog.CanBeShipped = inserted.CanBeShipped and test1backlog.PlannerCode = inserted.PlannerCode and test1backlog.rowguid = inserted.rowguid
Hi, I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA. Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2. Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers. Thank you!
Hi, I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA. Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2. Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers. Thank you! I know how to write a normal insert , update, delete triggers and using help from books online.. SO pls do it in useful.
Recently I am facing a problem as, I have a trigger on table1 in after insert event. When i tried to insert data on table from 4 different source deadlock problem for causing trigger fire. Whenever I removed the trigger no dead lock occured. Can any one help me to handle this deadlock problem.
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
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)
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float) RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float) AS BEGIN IF (@IncludeParent=1) BEGIN INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID END DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float DECLARE RetrieveReports CURSOR STATIC LOCAL FOR SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID OPEN RetrieveReports FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID) INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID) FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID END CLOSE RetrieveReports DEALLOCATE RetrieveReports
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr] for insert as begin declare @liter as decimal(28,6)
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.
declare @string varchar (5000) select @string='Update product Set Product_End_dt = '12/31/05' from ' + @table_nm + ' c inner join Booktable b On (c.book_id = b.book_id).............
exec (@string) -- I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help! Reddy.
Someone, I don't know who, deleted an important table in my database. I've only been able to find restore routines on full databases. Is there any way to restore just a single table?
How can I see which table is locked up by some particular process? I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem? I really appreciate your help..
where TABLE_NAME is the table name in database and COLUMN_NAME is name of the column in above table and the remaning are the attributes to that column. please suggesst me the sql query to get the following structre of any table in database,
Hi, anyone could help me on how to do the inner join of two table from two different database. Below is my scripts: Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN")) Dim sqlCommand2 As New SqlCommand("", SqlConnection1) Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN")) Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2) Sub Page_Load(Source as Object, E as EventArgs) sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'" sqlconnection1.Open() ............................ The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me. Ann123
