Multiple Triggers On A Table Or Encapsulated Triggers
May 12, 2008
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.
When 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
What 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
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'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.
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?
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?
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
My current situation is I have an application currently using windows authentication developed mostly in classic asp with a few .net 1.x files mixed in. The sql database uses hundreds of triggers to track user actions in nearly 40 tables:- table trigger recognizes that an update/insert is occurring, calls a user function that returns the user id (by using system_user), and along with the update/insert the user id and current date time are saved to the record.This is fine under windows authentication, but we're planning on integrating this application with several others under the same domain using forms authentication. My connection string for forms has everyone connecting to the database using a single login. Permissions, otherwise, are handled at the page level. I obviously have no plans of storing the password in some form of session variable. Consequently, under forms authentication, the database will be recognize everyone as the same person. Maybe I'm just missing the obvious solution, so what are your thoughts on a solution for this?
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
How do I set up an insert trigger to copy all of the inserted data to another table? In other words, when someone adds a new paramater in the params table, I want to automatically create a matching set of data in the goals table. Thanks,Krista
I have a question about a table with triggers or maybe a check constraint.I have the following create tables:
create table bid( seller char(10) not null, item_nummer numeric(3) not null, )
create table Item( startprice char(5) not null, description char(22) not null, start_date char(10) not null, end_date char(10) not null, seller char(10) not null, item_nummer numeric(3) not null, )
What i'm trying to make is this trigger/constraint: colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).
Hi 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 ?
I've imported data from an Excel spreadsheet to a table that has fields to match the destination table I'm trying to populate. The destination table has an Insert trigger with several checks on certain fields to make sure they have corresponding records in other tables.
If I do a statement like "INSERT INTO destinationTable ( ItemId, Product, SuperID, etc etc ) SELECT * FROM oldtable" it runs for a while then gives me error messages from the trigger and rolls back the Insert.
The trigger has code such as "IF (SELECT COUNT(*) FROM inserted WHERE ((inserted.Product Is Not Null))) != (SELECT COUNT(*) FROM tblInProduct, inserted WHERE (tblInProduct.Product = inserted.Product)) BEGIN (Error message code goes here) END"
So, do I need to do an INNER JOIN to each of the related files? When I try that, I get this error: "Msg 121, Level 15, State 1, Line 2 The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." Is SQL counting the foreign key fields as separate fields, or what?
I am looking to track any changes made to any table within a db into a single audit table which will hold as fields: the table that has been updated/inserted, the field that was changed, its primary key, the old value and the new value specific to that field, and the date it was updated/inserted.
From what I have read, it does not look like this is possible with a trigger on table as it is not row specific and that I might have to control this from the business layer (vb.net). I am correct in this assumption, or is there a way of tracking specific data changes through triggers.
I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?
I was hoping for some information regarding table triggers. We have databases that are part of an AlwaysOn availability group on SQL Server 2012. Some of the tables have table triggers defined. There are both types of triggers, INSTEAD OF and AFTER INSERT, etc.
From my understanding of how secondary read-only replicas work, I am assuming that these triggers have no impact at all when the database is a read-only secondary replica and they only fire when the database is the primary read-write database.
However, after doing a search on the internet, I have not been able to find a definitive source of information.
I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.Does anyone know the easy way to do this? / Syntax for the trigger?
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
Hello All, I have to write Trigger for Update, I have two tables, one is for storing records of current values, and one is for storing history of values. How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values. Help me. General Problem
I need to create a set of rows every time a new row is inserted into a table. Example (I think this would work)... select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id) Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values? Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger? Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value. SET @ActionTaken = 'A' IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
Hi All, I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
