I have a table which when certain columns are updated, need a trigger to fire to update a next schedule date in that same table for that record. I can write the trigger, but my question for performance and efficiency is which approach would be better. Separate triggers fo the 8 columns, or a large trigger with an If to check if these columns are updated.
Thanks
I have a question regarding Triggers.
Lets say I want to create a trigger for Insert, Update and Delete action. For each action I do different things.
Now I can create a single trigger for Insert, delete and update, and using counts for inserted and deleted table, take the action appropriately.
Or I can create three different triggers each for Insrt, update and delete.
Which option is better for performance?
Thanks
am using FOR UPDATE triggers to audit a table that has 67 fields. Myproblem is that this slows down the system significantly. I havenarrowed down the problem to the size (Lines of code) that need to becompiled after the trigger has been fired. There is about 67 IFUpdate(fieldName) inside the trigger and a not very complex selectstatement inside the if followed by an insert to the audit table. WhenI leave only a few IF-s in the trigger and comment the rest of thecode performance increased dramatically. It seems like it is checkingevery single UPdate() statement. Assuming that this was slowing downdue to doing a select for every update i tried to do to seperateselects in the beginning from Deleted and Inserted and assigningcolumns name to specific variables and instead of doingif Update(fieldName) i didif @DelFieldName <> @InsFieldNamebeginINSERT INTO AUDITSELECT WHAT I NEEDENDThis did not improve performance. If you have any ideas on how to getaround this issue please let me know.Below is an example of what my triggers look like.------------------------------------Trigger 1 -- this was my original designCREATE trigger1 on TableFOR UPDATEASif update(field1)begininsert into AuditSELECT What I needENDif update(field2)begininsert into AuditSELECT What I needEND...... Repeated about 65 more timesif update(field67)insert into AuditSELECT What I needEND---------------------------------------------------------------------------Trigger 2 -- this is what i tried but did not improve performanceCREATE trigger2 on TableFOR UPDATEASDeclare @DelField1 varcharDeclare @DelField2 varchar....Declare @DelField67 varcharSelect@DelField1 = Field1,@DelField2 = Field2,....@DelField67 = Field67From DeletedDeclare @InsField1 varcharDeclare @InsField2 varchar....Declare @InsField67 varcharSelect@insField1 = Field1,@insField2 = Field2,....@InsField67 = Field67From Inserted-- I do not do if Update() but instead compare variablesif @DelField1 <> InsField1beginInsert into AUDITSELECT what I needendif @DelField2 <> InsField2beginInsert into AUDITSELECT what I needend............if @DelField67 <> InsField67beginInsert into AUDITSELECT what I needend----------------------------------------------IF you have any idea how to optimize this please let me know. Anyinput is greatly appreciated. I do not have a problem with triggersdoing what they are supposed to, they are very slow this is myconcern. The reason I gave you two examples is because i suspect ithas something to do with the enormouse amount of code inside thetrigger. both examples perform about the same whether i use the twohuge selects from the Inserted and Deleted or not.Thanks,Gent
View 1 Replies View RelatedWhat would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire
View 1 Replies View RelatedI have a messy design that I can't change but need to keep in synch. I have a master table where a person's info is entered into. Upon that entry I have to take that info (name, bdate,ssn,location) and populate it to 4 separate tables in 4 separate databases, all of which have their own id field which has to be incremented (no none of them are identity columns). At the end of the process I have to update the master record with the id fields of all the other four tables. I was going to do this with an insert trigger. As far as protecting the id's across all four databases, I was going to use a begin trans and get the next id field for all four databases and then apply the logic to add the records. Does this seem a safe approach, or am I missing something? Do I need to do a begin tran for each database separately?
THanks
I have the following trigger that updates a couple test fields to null when they are 1/1/1900, works great on inserts, and one line updates:
CREATE TRIGGER UpdateDate
ON test
AFTER INSERT, UPDATE
AS
UPDATE Test
SET
[CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END,
[CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] END
where AutoID = (select AutoID from inserted)
However, when trying to do a multi line update statement, I get the following error:
Msg 512, Level 16, State 1, Procedure UpdateDate, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated
How can I get around this?
Hello,
Anyone know how to create an update trigger where the primary key isn't fixed.
If the primary key change how can I tie together the Deleted and Inserted tables if more than one row is updated?
/Patrik
I have a After insert, update trigger. When I update multiple records with unique constraints column in it update fails. But if this a single record update it works.
Could like to know the reason.
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
A server I'm working on has a very unique situation, where user tables and production tables reside on the same database. Users update / create tables or populates these tables, so it can't be a table-specific trigger. However, they give a new meaning to "kamikaze pilots" as it's not uncommon for them to "accidentally" update / insert / delete 500,000,000 + records in a single statement. I've tried educating them to use batching, but to no avail, so now I'm forced to stop these statements BEFORE they execute, based on rowcount, as they fill up the database log so quickly that it goes into recovery mode (It has a 200GB log file - insane, I know).
I recon the mosts transactions allowed should be 1,000,000 records in a single statement. Looking for database trigger to stop them from executing statements with large records?
Hi
I would like to know how can I write a trigger for a table that lists folders deleted. I have a table called RECORDS which contains folders and would like to know folders that are being deleted.
Thanks...
Hi,
I am trying to figure out which of these option is best suited for auditing. Although each one of them has its own pros/cons.
CLR trigger is easy to write and can be made generic so that it fits for any table required to be audited.
I tried both the option in test database and i found the CLR trigger performed poorly.
Results were :
For table A (3 columns) with TSQL trigger took less than a sec for 2500 sequential inserts.
While table B (3 columns) having same structure with CLR trigger took more than 20 sec for 2500 sequential inserts.
Has anybody done performance comparision of this 2 approaches ?
Please share results if any.
I wanted to validate that is my findings correct so that i select best optimized approach.
Thanks,
Jignesh
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 RelatedWhen a trigger on one table updates another table, does it activatethe update trigger on the updated table?For ex:1. Table A has a trigger for update2. Table B has a trigger for update that updates table AWill the trigger no table A run when table B trigger updates table A?Thanks
View 2 Replies View RelatedI am trying to understand how to use instead of trigger using an update.
Here are a few things that I need clarified.
- When using an 'instead of Update' trigger is this replacing any other Update that is happening to the Table.
- Or, is the occurence of the trigger happening after the update?
- Also, I have seen some syntax where people use:
Update table
set field1 = inserted.field1
from deleted, inserted, table
where deleted.field1 = ....
Does this mean that when using the 'instead of' option it keeps track of
what is being deleted and what is being inserted by using such things as deleted.field1 or inserted.field1?
What I need to do is check that the previous value in a particular field is null before updating it. I don't want to update a particular field that already contains a value in it.
Thanks.
I am new to trigger and I have following question.
I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts.
Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times?
I hope I make it clear and please help me on this! :o
I have a row that is being used log track plays on our website.
Here's the table:
CREATE TABLE [dbo].[Music_BandTrackPlays](
[ListenDate] [datetime] NOT NULL DEFAULT (getdate()),
[TrackId] [int] NOT NULL,
[IPAddress] [varchar](20)
) ON [PRIMARY]
There's a CLUSTERED INDEX on ListenDate ASC and a NON CLUSTERED INDEX on the TrackId.
I have a TRIGGER on the Music_BandTrackPlays table that looks like the following:
CREATE TRIGGER [trig_Increment_Music_BandTrackPlays_PlayCount]
ON [dbo].[Music_BandTrackPlays] AFTER INSERT
AS
UPDATE
Music_BandTracks
SET
Music_BandTracks.PlayCount = Music_BandTracks.PlayCount + TP.PlayCount
FROM
(SELECT TrackId, COUNT(*) AS PlayCount
FROM inserted
GROUP BY TrackId) AS TP
WHERE
Music_BandTracks.TrackId = TP.TrackId
When a simple INSERT statement is done on the Music_BandTrackPlays table, it can take quite a long time. When I remove the TRIGGER the INSERTs are immediate. The Execution plan for the TRIGGER shows that a 'Inserted Scan' is taking up most of the resources.
How exactly is the pseudo 'inserted' table formed?
For now, I think the easiest thing to do is update my logging page so it performs 2 queries. One to UPDATE the Music_BandTracks table and increment the counter, and perform the INSERT into the Music_BandTrackPlays table seperately.
I'm ok with that solution but I would really like to understand why the TRIGGER is taking so long. The 'inserted' pseudo table will be 1 row 99% of the time. Does SQL Server perform a table scan on all 20 million rows in order to determine what's new and put it in the inserted pseudo table?
Thanks!
Plz help
I try to use trigger to see if Component table update at same time update the AssemblySubcomponent
AssemblyID
but it say
Msg 4104, Level 16, State 1, Procedure trigupdateSubcomponentID, Line 6
The multi-part identifier "a.SubcomponentID" could not be bound.
the code look like
create trigger trigupdateAssemblyID on Component
for update, insert
as
begin
if update (ComponentID)
update AssemblySubcomponent set a.AssemblyID= i.ComponentD
from inserted i
join AssemblySubcomponent a on a.AssemblyID =i.ComponentID
end
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Â
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:
CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS
INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO
CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS
INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO
I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".
My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.
Gazzou
If I have a trigger on a field in a table, and I update one record trigger fire properly. If I do a update to that same field on all records in the table the trigger does not fire. I the error in the trigger, or do I need to change my update statement?
View 2 Replies View RelatedHello,
I would like to update foreign keys using triggers. At least thats what I think is the solution when having multiple references to a table in order to avoid the "cycles or multiple cascade paths" error message.
Anyway, here are three tables
Dentist table
dentist_id int identity (auto increment)
Patient table:
patient_id int identity (auto increment)
Appointment:
apointment_id int identity (auto increment)
id_dentist int FK to dentist_id
id_patient int FK to patient_id
I am gooling but cant find a way to make a trigger run only when the dentist_id from dentist table is updated. Also, is there a way to get the new id and old id somehow? I saw some posts with new.dentist_id and old.dentist_id but apparently is not for sql server.
Thank you.
SQL Server 2000 : I have a series of tables which all have the samestructure. When any of these tables are modified I need to syncrhoniseall of those modifications with one other table wich is a sort of mergeof the individual tables with one extra column.For most of these tables this is not a problem. The problem arriveswhen one of the tables has an ntext column which obviously can not beused in an update or insert trigger.Here's an example of one of them:CREATE TABLE tblImages(ID INT IDENTITY(1,1) PRIMARY KEY,Inventory nvarchar(8) NOT NULL,Coll nvarchar(8) NOT NULL,ImageFile nvarchar(128) NOT NULL,ImageNotes ntext NULL,TS timestamp NULLCONSTRAINT U_Images UNIQUE NONCLUSTERED (ItemCode, Inventory, Coll,ImageFile)I then had created an update trigger which looked like this:CREATE TRIGGER COLLNAME_UTRIGGER ON COLLNAME_ImagesFOR UPDATEASBEGINUPDATE tblImages SETInventory = inserted.Inventory,Coll = 'COLLNAME',ImageFile = inserted.ImageFileName,FROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Invventory = tblImages.Invventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileUPDATE tblImagesSET ImageNotes=inserted.NotesFROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Inventory= tblImages.Inventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileEND " & vbCrLf)The first update in my trigger, be it an update or insert trigger,works fine. It crashes with the "Cannot use text, ntext or imagecolumns in the 'inserted' or 'deleted' tables." error in the secondpart.I have read various messages through the Internet on this and severalof them reference using INSTEAD OF triggers and views. I have neverused those before as this is my first work with SQL 2000. None of theexamples of INSTEAD OF triggers I have seen yet use the actual insertedtables and I haven't quite understood how to use them correctly.Can someone help me with the basic syntax as this trigger is one ofseveral that I am going to have to get working.Thank you in advance for any help, assistance, suggestions or"direction pointing" you may provide.
View 1 Replies View RelatedHi
i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data
some people says you can update joined tables using instead of triggers
how is that ?is there any example ?
thanks in advance.
Hello,
the application will add items into a "bag". That is, the items in one table will refer a record in another table. This will be done in timely manner -- with second or minute delays between adding a new item. There will be up to thouthand of items per bag. The option is to wait until a full bag accumulates and set up all the references at once by using
UPDATE items SET container_ref = bag WHERE id IN [...]
The disadvantage of such all-at-once I see is inability to encapsulate the functionality into a SP -- the problem is to pass a set of IDs. The advantage should be efficiency in terms of total SQL Server load. How mush would it be?
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Many thanks for advice.
Hello,
I am trying to create a trigger to update a table on a different database server. (Both databases are SQL server 7.0) Does anyone know the syntax of how to implement this?
Any help is appreciated!!
Thanks,
Lisa
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,
Hi
There are 2 databases db1 and db2 on SQL server 7 with tables 1 and 2 respectively.
Both these tables have a field called Quantity. IF quantity gets updated in table 1 then that change should be immediately reflected in table2.
I though an update on table1 would serve the purpose but this trigger doesn't seem to be working however there are no errors during the syntax check.
The code for the trigger that i have used on table 1 is as below
CREATE TRIGGER [Qty_UPDATE] ON [Table1]
FOR UPDATE
AS
IF UPDATE (Qty)
BEGIN
UPDATE db2.dbo.table2
SET db2.dbo.table2.qty = qty
END
Please help
Thanks in advance
Hi all. I have this problem:
on insert in a new table a have to change one column before insert.
I wrote this trigger:
create trigger SUBSCR_ID_TRANSFER ON dbo.SalesOrderExtensionBase AFTER INSERTAS BEGIN SET NOCOUNT ON;DECLARE @OpportunityID uniqueidentifier;DECLARE @subscrId uniqueidentifier;declare @salesorderid uniqueidentifier;set @salesorderid = (select SalesorderID from inserted)SET @OpportunityID = (SELECT OpportunityId FROM SalesOrderBase where SalesOrderID=@salesorderid)SET @subscrId = (SELECT New_old_subscridId from OpportunityExtensionbase where OpportunityID=@OpportunityID)Update inserted set New_old_subscridId = @subscrIdENDbut SQL Rise the error "The inserted values can not be modified"
how to slove this issue.
Thanks.
I recently moved a database from a SQL server 2005 box to new server running SQL server 2012. The update/insert triggers that were working on the tables to handle referential integrity checking are no longer working. Running the same database on SQL Server 2008 and everything works.
Here is one of the trigger that throws the error 44446
USE
[M2Data]
GO
/****** Object:Â
Trigger [dbo].[tblContacts_UTrig]Â Â Â Script Date: 3/11/2014 9:07:13 AM ******/
SET
ANSI_NULLS ON
[code]....
I have verified that there is a matching key in tblCompanys. Also, when I run a query to update tblContacts the error message appears but the update does take. If I try and edit the row directly by selecting edit top 200 rows - the error message appears and the update does NOT take.
These triggers were probably added to the database during an upsize from MS Access to SQL Server 7 way back. What am I missing - is it something in the syntax?