Triggers And Replication
Jul 23, 2005If a table is updated via replication, will an INSERT trigger go off if
a new row is being added?
--Bob
ADVERTISEMENT
DB Replication Or Table Replication Via Triggers?
Apr 17, 2007Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies View RelatedTriggers And Replication
Aug 25, 2000Is there a way to not initiate triggers during replication? We have a few triggers that we do not want to use while replicating. They cause data in other tables to be deleted.
View 2 Replies View RelatedReplication Of Triggers
Jun 21, 2006Hello !
I have set up some transactional replication on one of my database but I can't see where I can specify that we should replicate the triggers. What I have seen is that the triggers are automatically replicated when the table is replicated except id they are marked "Not for replication"
But it does not seem to work for me. Do you have any idea ?
Thanks,
Removing Replication Triggers.
Jan 13, 2005Does anyone know how to remove 'Replication Triggers'?
I have a database created by export of data object. This process does not export any system tables to do with replication but there still seems to be something in the database which references them. There is no poblem if I backup and restore the dataabse.
Any advice?
Thanks Ed
How To Configure Replication Triggers To SET NOCOUNT ON?
Jul 16, 2007Hopefully simple question:
How to configure replication so that replication triggers issue a SET NOCOUNT ON?
Also: are there any negative consequences of adding a SET NOCOUNT ON to replication triggers?
I'm not a DBA so my experience with replication is pretty much non-existent. Hopefully, there's a simple configuration option that I can apply when setting up replication for a database. If not, is it going to be feasible for me to write a script that runs over the replication triggers/procs to add this SET statement (any pointers on inbuilt procs that would help here would be real useful)?
Thanks in advance
Christian
Merge Replication Corruption (system Triggers And Views)
Sep 21, 2006All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:
Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?
-mike
Creating Triggers In Transactional Replication On The Subscriber Side
Jun 17, 2007Hi all
i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?
Thanks,
Arslan.
Multiple Triggers On A Table Or Encapsulated Triggers
May 12, 2008This 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
SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.
Jun 15, 2007I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies View RelatedReplication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication
Sep 13, 2007Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies View RelatedAdding New Table In Replication And Changing One Column Replication Database
Jan 17, 2002Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
DBCC OPENTRAN Shows REPLICATION On A Server That Is Not Configured For Replication
Aug 22, 2007Hello,
I have this problem on a Production database.
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Thank you.
Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.
Mar 6, 2007Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View RelatedReplication :: Difference Between Snapshot And Transaction And Merge Replication?
May 26, 2015What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedSQL Express Replication And Problems Accessing Data After Replication
Jul 28, 2006Hi,
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
Any advice appreciated !
Thanks
Ronan
Does Replication Affect Tempdb GROWTH Replication
Jun 28, 2007Hi all,
I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????
Thanks in advance
Jacx
How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?
Aug 30, 2007Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
Replication :: Transactional Replication Removal Sequence
Sep 2, 2015I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. Â I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. Â I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. Â SQL-P (publisher),Â
SQL-D (distributor) and SQL-S (subscriber). Â Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
Triggers
Jan 3, 2007Is 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.
View 1 Replies View RelatedTriggers
May 31, 2007Hello 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
Triggers - Is This Possible?
Jun 15, 2007I 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.
Triggers
Dec 6, 2007
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
Triggers In .net
Dec 21, 2007Hi 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,
View 3 Replies View RelatedTriggers
May 9, 2004Hi 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?
Thank you,
Mike
SQL Triggers
Jun 17, 2004When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set
(Lastname = @Lastname)
where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
Instead Of Triggers
Mar 5, 2006I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
View 3 Replies View RelatedTriggers
Dec 4, 2001l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR insert,Update AS
IF left('Loan_No',3)='ABC'
update Test_TRG
set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF'
update Test_TRG set
loanbook = 'NEWDEF'
where loanbook is null
else
update Test_TRG
set loanbook =left('Loan_No',3)
where loanbook is null
Triggers
Dec 5, 2001I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ".
The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if
The left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=KVS and its null then NEWKVS
left(loan_no,3)=MFS and its null then MFS
left(loan_no,3)=TCR and its null then TCR
left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained
in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it
l get an error.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR INSERT, UPDATE
AS
UPDATE Test_TRG
SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG'
WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS'
WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS'
WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 )
END
FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK
-- Fill in Primary Key or other Join Column(s)
WHERE LEFT( i.Loan_No, 3 ) = 'MCG'
OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL )
OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Triggers
Jan 11, 2002I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
Triggers
Jan 31, 2002Hi! i have a simple question...is possible to create a trigger who affect multiple tables?
The idea will be something like this:
create trigger mytrigger
on sales, users
as...
of course, this don´t work :)
Triggers
Aug 3, 2001Hey everyone...
I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...
Thank you...
Triggers
Oct 24, 2001I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:
CREATE Trigger upd_cost_master On tbl_cost_master
For Insert, Update As
Declare @U_Name varchar(65)
Declare @O_Name varchar(65)
Select @U_Name=I.submitted_by, @O_Name=O.submitted_by
From tbl_cost_master O Inner Join Inserted I On O.autokey = I.autokey
Print 'Inserted: ' + @U_Name
Print 'tbl_cost_master: ' + @O_Name
Commit Tran
To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?
Chris