How Do You Conditionally Fire A Trigger (mimic Replication)
Oct 19, 2006
Does anyone know how to do the following. I'm trying to mimic
replication with triggers.
I have 2 databases, each have these 2 tables.
1.
USERS
ID int
NAME varchar(20)
2.
CHANGES
TABLE varchar(20)
TYPE varchar(10)
col1 varchar(20)
col2 varchar(20)
On the USERS table I have a for insert trigger. Whenever a new user is
added the trigger puts an entry into the CHANGES table such as
("USERS", "INSERT", "1", "Fred")
I now have an application (vb.net) that monitors the CHANGES table on
server1. If it finds an entry it determines the table using the TABLE
column and performs the necessary insert and deletes the entry from
CHANGES. Now the problem is server2 also has an for insert trigger on
the USERS table so it puts an entry into CHANGES on server2. As you
can imagine this goes around in a loop.
What I was hoping for was someway of saying, "I'm inserting from my
application so don't do the trigger".
Is there a way to make a sql table trigger fire off conditionally on calling app?We have this vendor app database which also have in-house custom built app operates on them. Some one are thinking using triggers to audit second part of the app activities, but want to skip the vendor app part of operation for at least performance reason.To my knowledge, the answer is no. An insert trigger will fire off whenever there is an insert. It doesn't provide a mechanism to check which app/process first, then conditionally fire or not fire.
Is it possible to create a trigger to monitor when a value changes from 1 to 0 in a particular row. Example, rows have names, pins and status columns. When the status column of a row changes from 1 to 0, I am going to send a mail item to the person whose name has had the status changed.
I am using SQL Server 2005 database for a webbased application built on .net 2.0. Here is my situation. I have a SubmitDate and a Status field in a table. I want the trigger to fire based on the datevalue in the SubmitDate field. SubmitDate field is the last date for the user to submit an application. For eg: if the SubmitDate value is 03/10/07 , I want the trigger to fire on :01 of 03/11/07 to change the Status field value to Inactive. Is that possible? Thoughts on how to do it?
I have a trigger on UPDATE on a table. I'm running some maintenanceUPDATE and DELETE queries which I want the trigger to ignore but at thesame time I want other UPDATE queries that other users might be runningto keep triggering the trigger. Is there a SET statement perhaps thatI could set before my query? Or a clause in the UPDATE statement?This is on MSSQL 2000 server, on Win2k3
IF OBJECT_ID('dbo.AuditLog','U') IS NULL BEGIN CREATE TABLE [dbo].[AuditLog]
[code]....
[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL, [DateCreated] [datetime] NOT NULL, [TableName] [nvarchar](200) NULL,
i have the Audit_log table and the trigger above on Account table..it is working fine and i have some 1000 records in Audit log table , but it did not fire for some customers and i found trigger did not fire for 60 plus customers any possible reasons ??? that trigger did not fire ? Below are the timings account have been created but trigger did not log them
Is there a way to fire a trigger when a particular user (with sql login) logs into sql server? We are planning to use the above trigger to start a job which inturn records some inf.
A job runs every morning at 3.00 am to back up a database. Many of the tables have triggers on to write updates, deletes and inserts to audit tables.A typical trigger looks like this.
USE [ProjectDB_Live] GO /****** Object: Trigger [dbo].[trgStakeHolders] Script Date: 03/02/2015 10:23:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[code]....
I added a trigger to a table over the weekend - structured the same as the one above and, when the back up ran last night, it copied every row in the table into the audit table as if every row in the table had been updated.
I have tables that I want to fire either an update or insert trigger on.I could write a script containing a long list of inserts but I'm looking forsomething simpler. Would isql work? Any special conditions to get it towork?I've tried tricks like 'update x set col = col' or 'update x set col = col +'' 'All the alternatives seem to have problems. Any ideas?----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Hi.I need to make a trigger which fires when after an UPDATE is done ontable A in database X. The trigger must perform a statement whichcopies some of the data from the update to table B in database Y onthe same server.My question is: Can I access the data which caused the trigger to fireor just get an indication of which entry in table A had been updated?Best RegardsJens Christian Andersen.
I created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.
I am sure someone must have run into this before. I have a couple of tables with a parent child relationship.
I created a trigger on the insert of the parent but don't want it to fire until both the parent and child have been inserted into.
However sometimes the child may not get inserted in to at all. In other words it is a 1 to 0 or more relationship.
I created the whole insert into the parent and the child and wrapped it all up in a transaction hoping that the trigger would not fire until the transaction actually completed.
However such is not the case and it fires when the parent is inserted into but nothing is inserted into the child yet even though that is part of the transaction.
Is it possible to postpone trigger fire until after both parent and child table values have been inserted?
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure: USE [DB1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated the data in row 1 was not committed Error source .Net SqlClient Data provider. Error Message: the operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
I have a transactional replication environment that creates subscribers on another server as a staging area for an ETL process to a data warehouse application on a 3rd server which is the report repository. Currently the ETL process runs every 10 minutes and performs it's function across approx 150+ subscriber databases and consolidates it to the data warehouse.
I have an SLA of 2 minutes. I'd like to rework the ETL process (which run as SSIS job at the moment) to be specific to a single database and fire that one ETL proces when changes have been applied to that subscriber database only. Of these 150+ databases generally only about 8-10 are updating the subscriber at any given time per Repl Monitor. I'm thinking that if I only have a few transactions to apply to a single db the ETL would run in seconds dynamically as the subscriber is update.
The issue is how to fire the ETL process upon completion of updates to the subscriber DB? I'm thinking of using SP_Start_job passing the DBID to update the warehouse but unsure whether this is possible but if so where to trigger it.
On an existing Merge Pull Subscription (Subscriber: SQL Express) via websync how can we start replication via vb.net? We can use the cmd line replmerge.exe and all works fine however we would like this to be cleaner as it's going to be on demand sync. I saw in BOL about the RMO and how to create pubs/subs but nothing telling me how to actually start the replication process. Is there a sample of how to start the replication process in vb.net for websync? Thanks in advance.
Recently, we had some tables inadvertantly dropped from a 20+ GB data warehouse in the middle of one of our load cycles. In order to avoid restarting the load, we had to make restore a copy of the database from a full database backup and then manually move in the dropped tables using DTS.
This ended up being a painful process, and I am looking to avoid it by restructuring the database into filegroups so I can mimic the table restore function from 6.5.
I am looking for pros, cons, gotchas, and best practices regarding splitting a MSSQL 7 database into multiple filegroups. We would be including 1 or more tables and all of thier indexes in each filegroup. Specifically, I have the following questions:
1. All the literature I read mentions the need for doing transaction log backups and restores if I utilize filegroup backups. Is this really necessary? We have turned off transaction logging for performance reasons, and since we completely control the addition of new data into the db.
2. Are there implications for disk read performance involved with filegroups? Our data resides on an EMC disk array. I feel that we'll most likely have between 20 and 30 filegroups.
3. If we decide to move a table (and its indexes) between filegroups, is the process as simple a dropping the clustered index and recreating it on the new filegroup (then dropping and recreating the n-c indexes)?
Any information y'all can post would be greatly appreciated.
I'm trying to mimic the rowcount transform's custom property called VariableName in a custom pipeline transform, with the same drop-down and list of variables in the default property grid as that of "RowCount".
Should a UITypeEditor or a TypeConverter be used for such a custom property? More importantly, in either case, how do you get from the UITypeEditor or TypeConverter to the TaskHost which contains the Variables to enumerate?
I'm not that familiar with Windows Forms, but it appears in both cases (editor or type converter), an ITypeDescriptorContext parameter named "context" is passed, but I'm certain how to get from A to B, that is, from the context variable to the TaskHost.
P.S. There's no need to add a new variable, just select from those that already exist.
The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.
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
I have setup transactional replication between 2 computers.
In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion of any rows in a table. If an attempt to delete rows in that table occurs, this trigger will write the deleting rows to a local file C:
empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv, and then email this file to a list of recipients. This trigger only exists in the publisher.
Yesterday, while I'm working on the publisher DB, I accidentally executed a stored procedure which attempted to delete some rows in the table. The trigger fired, a file C:2012.09.04.09.01.01.tbl_deleted.csv generated, and the email was sent. But then problem occurs.
Problem: When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said: Command attempted:
Similar errors occur periodically, and the filename (C: empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv) in the error messages reflect the actual time of the error.
Hi,I have an issue with my replication at the moment. I will try todescribe the scenario accurately.I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connectto the publisher to upload/download changes. I have a trigger set up onone table which updates another, here is an example of the trigger:"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotesFOR INSERT, UPDATE, DELETEASdeclare @projTotal as moneydeclare @projId as intdeclare @projcurrtype as intselect @projId = project_id from insertedselect @projcurrtype = proj_curr_type from qt_projects where project_id= @projId--Get project total from the sum of table [qt_quotes]select @projTotal = (selectsum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@projcurrtype))as quoteTotal from qt_quotes where project_id = @projId)--Update projects record with new project totalupdate qt_projectsset proj_act_totl = @projTotalwhere project_id = @projId"I feel my trigger maybe setup incorrectly in that replication thinks aninsert is occurring instead of an update. (Im quite new to triggers)What is happening is a conflict is occuring with the following message:"The row was inserted at Server.Publisher' but could not be inserted at'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGNKEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred indatabase 'Publisher', table 'qt_projects', column 'project_id'."What is also happening as a result of this conflict (I think) is therecord in question is getting deleted from the Publisher. This iscausing huge problems as it is proving quite difficult to get theserecords back in the system due to identity values.Can anyone guide me to what might be happeing here, is it the trigger?
What problems do trigger driven replication impose? I've got two SQL servers. When one is updated, triggers will automatically update the information on the production server. What problems can arise from this? Does it create problems when mass updates and inserts are being performed on tables with triggers? Thanks
I am testing a merge publication with a push subscription. The publication contains a single table. That table has an INSTEAD OF UPDATE trigger marked NOT FOR REPLICATION and an AFTER trigger that needs to go off during replication.
During Synchronization neither trigger goes off. If I drop the instead of trigger the after trigger will go off. What is going on?
Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.
Here is the problem:
When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:
The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).
Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.
We are getting this behavior on several SQL Server 2005's...
Additional notes on the environment:
-CLR Triggers written in VB, on most tables performing application functions
-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.
-Again this problem only exists on some tables in the database.
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record. The second trigger inserts record in to another database table and also updates a column of the table "Sample". The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser. Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
This is probably a stupid question, but here goes: I am trying toupdate a table differently based on what is in one of the columnswithin the table. For example, I want to set Fld1 to be "On" if Fld2is "A", and I want to set Fld1 to be "Off" if Fld2 is "B". This iseasy to do in two SQL statements, but I was wondering if there's someway to do it in a single SQL statement. Thus far, I have had no luckin figuring out how to do this. Any ideas would be appreciated!!
I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!
Sample dataset (select * from cs_view):
Gender | Program | Student | Class_Section | Heads | Credits ------ | ------- | ------- | ------------- | ----- | ------- Female | English | Elena | Phys 101-b | 1 | 4 Female | English | Elena | Hist 101-c | 1 | 4 Female | English | Elena | Engl 101-a | 1 | 4 Female | English | Elena | Engl 105-b | 1 | 4 Male | History | Rich | Phys 105-a | 1 | 4 Male | History | Rich | Engl 101-c | 1 | 4 Male | History | Rich | Hist 101-b | 1 | 4 Male | History | Jacob | Phys 101-a | 1 | 4 Male | History | Jacob | Hist 101-b | 1 | 4 Male | History | Jacob | Engl 101-c | 1 | 4 Male | History | Jacob | Phys L-101-a | 1 | 0
Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.
Desired table: Gender Program Heads Credits ------ ------- ----- ------- Female English 1 16 Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.
