Replication Error Due To Trigger
Sep 4, 2012
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:
if @@trancount > 0 rollback tran
Error messages:
Attachment file C: emp2012.09.05.01.01.43.36.tbl_deleted.csv is invalid.
(Source: MSSQLServer. Error number: 22051)
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.
View 3 Replies
ADVERTISEMENT
May 14, 2004
We still have old SQL 6.5 with sp5a running and push replicate to 5 remote sites.
Is there a way to avoid a trigger to avoid to be replicated as in SQL2000 with "NOT FOR REPLICATION"?
Since there is no way to do a constraint check to accross table, is there any way to do a cross table check without using a trigger?
thanks
-D
View 1 Replies
View Related
Feb 21, 2002
Hi Guys,
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
View 1 Replies
View Related
Jan 4, 2007
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?
View 8 Replies
View Related
Jun 15, 2007
I'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 Related
Oct 19, 2006
Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE 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 isadded 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 onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.
View 9 Replies
View Related
Apr 28, 2008
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
View 3 Replies
View Related
Jul 14, 2006
Kill may be overdramatic, but...
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?
Thanks,
View 11 Replies
View Related
Jun 5, 2006
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:
execute sp_mapdown_bitmap 0x0000000040, @bm output
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.
Any assistance would be greatly appreciated.
View 18 Replies
View Related
Feb 2, 2007
Hi,
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??
It would be helpful if u could reply soon.
Thanks and Regards,
Swapna.B.
View 12 Replies
View Related
Mar 6, 2007
Hello,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 Related
May 22, 2002
I get this error in my Agent after starten to synchronise.
I see the access mdb been created and then deleted and renamed to
name_old.mdb
Do you have some help?
Richard
View 1 Replies
View Related
Jun 2, 2008
Hi:
I have created the following trigger. alter trigger deletecategories on Product_Tbl_ProductDetailsfor delete as delete CategoryID from Product_Tbl_Catagory,Product_Tbl_ProductDetailswhere Product_Tbl_Catagory.CategoryID=Product_Tbl_ProductDetails.CategoryIDdelete SubcategoryID from Product_Tbl_SubCatagory,Product_Tbl_ProductDetailswhere Product_Tbl_SubCatagory.SubcategoryID=Product_Tbl_ProductDetails.SubcategoryID While deleting the record "delete from Product_Tbl_ProductDetails where CategoryID=1"I have encountered the below error Error:Msg 208, Level 16, State 1, Procedure deletecategories, Line 4Invalid object name 'CategoryID'. Can any one send me the correct query Thanks:Suresh
View 1 Replies
View Related
Jun 6, 2002
When I execute a query that updates multiple rows, the trigger issues an error. Does anyone see the problem ?
Listed below is the Error, Trigger, and Query.
Server: Msg 512, Level 16, State 1, Procedure TR_KYLAB_LOT_GRADE_01, Line 5
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.
CREATE TRIGGER TR_KYLAB_LOT_GRADE_01
ON KYLAB_LOT_GRADE
FOR UPDATE NOT FOR REPLICATION
AS
Update KYLAB_LOT_GRADE SET
updateUserName = user_name(),
updateDateTime = getdate()
WHERE KYLAB_LOT_GRADEID = (
SELECT INSERTED.KYLAB_LOT_GRADEID FROM INSERTED)
UPDATE KYLAB_LOT_GRADE
SET OLD_ITEM = 'TEST'
WHERE ITEM_TYPE_ID = 'FG'
AND ITEM_NO = '2011312LV0'
AND LOT_NO IN ('1000', '1001')
View 1 Replies
View Related
Mar 28, 2001
Hi everybody,
My SQL 2000 refuses to recognize the INSTEAD OF trigger. I just pasted a query from the
SQL Server Books Online article "Designing INSTEAD OF Triggers" into the Query Analyzer and was greeted
with:
Server: Msg 170, Level 15, State 1, Procedure IO_Trig_INS_Employee, Line 2
Line 2: Incorrect syntax near 'INSTEAD'.
What am I missing please?
Thanks,
Boris
View 1 Replies
View Related
Mar 4, 2008
All-
The following trigger is in a table called "PERSON". When I attempt to save it (not even testing it yet), I get the message "Incorrect syntaxt near 'GO'". Please advise as to what the error might be.
CREATE TRIGGER InsertHeadCount
ON dbo.person
AFTER INSERT
AS
INSERT INTO
HEADCOUNT(person_id,activity_id,no_answer,not_coming,coming)
SELECT i.person_id,a.activity_id,1,0,0
FROM INSERTED i
CROSS JOIN ACTIVITY a
GO
Background: I'm building an application that includes tables PERSON, ACTIVITY, and HEADCOUNT that look like this:
PERSON
person_id (int)
person_name (varchar(50))
...etc
ACTIVITY
activity_id (int)
activity_name (varchar(50))
no_answer (bit)
not_coming (bit)
coming (bit)
HEADCOUNT
headcount_id (int)
member_id (int)
activity_id (int)
...etc
The intended functionality of the trigger is as follows: Suppose that:
a) The ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.
b) The HEADCOUNT table is already populated with 30 records.
TRIGGER OBJECTIVE: When a new record is added to the PERSON, say with person_id= 10, insert one record in the HEADCOUNT table FOR EACH activity in the ACTIVITY table for that person. Thus, if person #10 is added to the PERSON table, then the trigger would add the following records to the HEADCOUNT table:
headcount_id person_id activity_id
31 10 1
32 10 2
33 10 3
Thanks a lot in advance for your assistance!!!
-Kurt
View 2 Replies
View Related
Jul 23, 2005
I made a trigger for delete just like this."CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]FOR DELETEASDeclare @severity int,@IdNmbr nvarchar(10)Set @Severity = 0Declare NoId Cursor Local Static forSelect [Promo_Id] from deletedOpen NoIdWhile 1=1BeginFetch NoId Into@IdNmbrIf @@Fetch_Status <> 0BreakIf Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@IdNmbr)Set @Severity = @Severity+1ElseBeginDelete From MbPromo_hdr where [Promo_Id]=@IdNmbrEndEndClose NoIdDeallocate NoIdIf @Severity = 0CommitElseBeginRollBackPrint 'Data Cannot Be delete'EndGo"When i delete the record from Enterprise manager it give me an error"Another user has modified the content of this table or view. The databaserow you are modifying no longer exists in the database."Why? And it happen with all of the record at my table--Message posted via http://www.sqlmonster.com
View 3 Replies
View Related
Jul 23, 2005
I have a table:----------------------------------------------------CREATE TABLE CATEGORY (CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))GO----------------------------------------------------and i try to create this trigger, but i fail:----------------------------------------------------CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]FOR INSERTASDECLARE @TEMP_ID AS INT, @COUNTER AS INTUPDATE AI_CATEGORYSET @TEMP_ID = CATEGORY_ID, @COUNTER = 1IF PARENT_CATEGORY_ID IS NOT NULLBEGINWHILE@TEMP_ID IS NOT NULLBEGINUPDATE AI_CATEGORYSET @TEMP_ID = PARENT_CATEGORY_ID,@COUNTER = @COUNTER + 1ENDENDUPDATE CATEGORYSET DEPTH = @COUNTER----------------------------------------------------And i get the following Error using SQL Server 2000:Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7Invalid column name 'PARENT_CATEGORY_ID'.Any clue?Coosa
View 33 Replies
View Related
Jan 21, 2008
Hi all,
I'm trying to create an audit trigger and I have the following code:
Code Block
Create Trigger dbo.tu_paymentaudit
ON dbo.Payments
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()
BEGIN TRY
Insert INTO dbo.tblPaymentAudit
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',INSERTED.Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.tblPaymentAudit
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',INSERTED.Charges,@now, suser_sname()
FROM INSERTED
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
But when I try to create/execute it, it keeps coming back with the following error:
Msg 4104, Level 16, State 1, Procedure tu_paymentaudit, Line 10
The multi-part identifier "INSERTED.Charges" could not be bound.
Can anyone tell me what's wrong please?
Thanks
View 1 Replies
View Related
Oct 21, 2004
I have the following
CREATE TRIGGER dbo.tgrCacheCustomers
ON dbo.Customers
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask 'C:DependencyFile.txt','SELECT top 1 CustomerId FROM customers'
and I get the following error that I dont understand:
Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.
Any ideas someone?
View 2 Replies
View Related
Mar 23, 2001
The trigger does set my CommAudit field to 1 when imaSlsCls is changed,
but at the end of the day when I issue the UPDATE command to set all CommAudit back to 0 I get an error. Is there a differant way to make it work?? See trigger, update command & error below.
Thanks for any help,
Tom
*** TRIGGER ***
CREATE TRIGGER [tblItemMaster_utr] ON dbo.tblItemMaster
FOR UPDATE
AS
IF (select imaSlsCls from inserted) <> (select imaSlsCls from deleted)
BEGIN
update tblItemMaster set commaudit = 1
from inserted, tblItemMaster
where inserted.imaitnbr = tblItemMaster.imaitnbr
END
*** UPDATE COMMAND ***
UPDATE tblItemMaster SET CommAudit = 0 WHERE CommAudit = 1
*** ERROR ***
Server: Msg 512, Level 16, State 1, Procedure tblItemMaster_utr, Line 5
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.
View 2 Replies
View Related
Oct 5, 2005
I have SQL Server 2000, and our web application is in WebObjects.
I built a trigger on a table that indicates if certain fields in a record have been changed since the last time a report was run from the application.
This trigger runs fine through the Query Analyzer, and runs fine with a direct input through enterprise manager. However, when the WebObjects application tries to update the table, and error is thrown.
Is anyone familiar with a reason why an application would throw an error on an update, when the DB tools do not? If we disable the trigger, the application has no problem updating the table.
Here is the relevant portion of the trigger:
create trigger t_press_run_change
on dbo.press_run_line_item
for insert, update, delete
as
updatepress_run_line_item
setis_changed = 1
from deleted d
join press_run_line_item p on p.press_run_line_item_id = d.press_run_line_item_id
where(p.is_changed = 0 or p.is_changed is null)
AND ((isNull(p.print_quantity,0) <> isNull(d.print_quantity,0))
OR (isNull(p.spoilage_pct,0) <> isNull(d.spoilage_pct,0))
OR(isNull(p.ad_dimension_id,0) <> isNull(d.ad_dimension_id,0))
OR(isNull(p.quarter_fold_id,0) <> isNull(d.quarter_fold_id,0))
OR(isNull(p.max_qty_per_shipment,'') <> isNULL(d.max_qty_per_shipment,''))
OR(isNull(p.packaging_max_height,'') <> isNull(d.packaging_max_height,''))
OR(isNull(p.packaging_max_weight,'') <> isNull(d.packaging_max_weight,''))
OR(isNull(p.packaging_skids,'') <> isNull(d.packaging_skids,''))
OR(isNull(p.packaging_turns,'') <> isNull(d.packaging_turns,''))
OR(isNull(p.packaging_cartons,'') <> isNull(d.packaging_cartons,''))
OR(isNull(p.preprint_delivery_time,'') <> isNull(d.preprint_delivery_time,''))
OR(isNull(p.contact_id,0)<> isNull(d.contact_id,0))
OR(isNull(p.address_company,'')<>isNull(d.address_company,''))
OR(isNull(p.address1,'')<>IsNull(d.address1,''))
OR(isNull(p.address2,'')<>IsNull(d.address2,''))
OR(isNull(p.address_city,'')<>IsNull(d.address_city,''))
OR(isNull(p.address_state,'')<>IsNull(d.address_state,''))
OR(IsNull(p.address_zip,'')<>IsNull(d.address_zip,''))
OR(isNull(p.address_Country_id,'')<>IsNull(d.address_country_id,''))
OR(isNull(p.client_printer_id,'')<>IsNull(d.client_printer_id,'')))
View 2 Replies
View Related
Apr 3, 2008
Hi,
Please can anyone help me code the trigger below. I have had errors pointing to "sp_configure 'SQL Mail XPs', 1;" and "RECONFIGURE", that this lines should be not where I have placed them. I have tried other combinations and have not worked at all! Is any other ways I can achieve this all?
Code Snippet
CREATE TRIGGER reminder
ON Bug_Report
FOR INSERT, UPDATE, DELETE
AS
sp_configure 'SQL Mail XPs', 1;
EXEC master..xp_sendmail 'MaryM',
'Don't forget to print a bug report.'
RECONFIGURE
View 3 Replies
View Related
Oct 17, 2007
hi.......
i create a trigger after delete which work perfactly fine on one sqlserver2005 machine but not at another.......machine having sqlserver2005
this trigger effect 15 tables from which i am deleting data...when trigger fires.....
now it cause following error when i fire ir on another machine
error source: .net sql client data provider
error message:maximun stored procedure,triggers,functions,or view nesting level exceeded (limit 32)
plzzzz give da solution.....its really frustrating me....
thanks in advance
View 5 Replies
View Related
Apr 25, 2002
The many that I have spoken to all are clueless on this one. Thanks in advance for the right solution!
The insert trigger I created works fine (well, nearly fine), except that AFTER the first insert operation (ie second, third etc), it always produces the correct results BUT FOR THE PREVIOUS INSERTED ROW. It is as if there is a latency of one row in the temp table INSERTED.
I would greatly appreciate a 'why', and more importantly, a 'how to fix it' for this problem.
If you need to look at the code, a NOTEPAD file is attached.
Much appreciated
--start trigger--
create trigger UpdateAffiliateEarnings
on Orders
for insert, update
as
--declare variables
declare @ProductType varchar(15),
@AffID int,
@Earnings money,
@CurrentEarnings money,
@AffTotalEarnings money,
@AffTotalPayments money,
@AffOutstandingBalance money
--check existence of affiliateid, and for product type
select @AffID=AffID, @ProductType=Source
from Orders
where AffID IS NOT NULL
--get relevant information
if @AffID IS NOT NULL
begin
if @ProductType = 'FLOWER'
begin
select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te)
from Orders o,FlowerOrder t,Commission c
where o.OrderID = t.OrderID
and t.CommissionID = c.CommissionID
and PaymentConfirmedYN='YES'
end
if @ProductType='PHONE'
begin
select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te)
from Orders o,PhoneOrder t,Commission c
where o.OrderID = t.OrderID
and t.CommissionID = c.CommissionID
and PaymentConfirmedYN='YES'
end
--update Affiliate account
--get totals to update
select @CurrentEarnings=AffTotalEarnings, @AffTotalPayments=AffTotalPayments
from Affiliates
where AffID=@AffID
--calculate new totals for affiliate account
set @AffTotalEarnings=@CurrentEarnings+@Earnings
set @AffOutstandingBalance=@AffTotalEarnings-@AffTotalPayments
--update affiliate account to new totals
update Affiliates
set AffTotalEarnings=@AffTotalEarnings, AffOutstandingBalance=@AffOutstandingBalance
where AffID=@AffID
--roll back the transaction if there is an error
if @@ERROR !=0
rollback tran
end
-- end of trigger --
View 1 Replies
View Related
May 10, 2006
Hi,
Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:
sql Code:
Original
- sql Code
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync
ON smvcModImageManagerCategory
FOR INSERT, DELETE
AS
DECLARE @pdfId INTEGER;
DECLARE @parentPdfId INTEGER;
DECLARE @grandparentPdfId INTEGER;
DECLARE @parentId INTEGER;
DECLARE @grandparentId INTEGER;
DECLARE @tableName VARCHAR( 255 );
-- If I am being inserted or deleted, and I am not a top level
-- category, then my parent's pdf record needs to be set so that
-- the pdf file is updated
IF (SELECT id FROM Inserted) IS NOT NULL
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Inserted),
@parentId = (SELECT parentCategory FROM Inserted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId);
END
ELSE
BEGIN
SELECT @pdfId = (SELECT pdfManagerId FROM Deleted),
@parentId = (SELECT parentCategory FROM Deleted),
@grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory
WHERE id = @parentId)
END
-- If I am not a top level category, set my parent's pdf to be
-- updated
IF @parentId <> -1
BEGIN
SELECT @parentPdfId = (SELECT pdfManagerId
FROM smvcModImageManagerCategory
WHERE id = @parentId);
UPDATE smvcModPdfManager SET data_last_updated = GETDATE()
WHERE id = @parentPdfId;
END
GO
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO
However, when I execute a statement like:
sql Code:
Original
- sql Code
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.
Thank you.
View 1 Replies
View Related
May 9, 2014
I have a syntax error...
[code = "sql"]CREATE TRIGGER Trigger_20321 ON FACT_CUST_GRP_ICM_MO
AFTER DELETE
AS
/* DELETE trigger on FACT_CUST_GRP_ICM_MO */
/* default body for Trigger_20321 */
[Code]...
Msg 102, Level 15, State 1, Procedure Trigger_20321, Line 21
Incorrect syntax near ')'.
The error is in line: raiserror (@errno, @errmsg)
View 1 Replies
View Related
Apr 15, 2014
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?
View 5 Replies
View Related
Mar 17, 2006
I need a trigger (well, I don't *need* one, but it would be optimal!)but I can't get it to work because it references ntext fields.Is there any alternative? I could write it in laborious code in theapplication, but I'd rather not!DDL for table and trigger below.TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblMyTable]GOCREATE TABLE [dbo].[tblMyTable] ([fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTableFOR INSERTASBEGINIF UPDATE(fldKBSubject)BEGINUPDATEtblMyTableSETfldSubject = i.fldKBSubjectFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDIF UPDATE (fldKBDescription)BEGINUPDATEtblMyTableSETfldDescription = i.fldKBDescriptionFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDEND
View 3 Replies
View Related
Jul 24, 2007
I have a DDL Trigger that writes a record into a table when any DDL event happens.
It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.
Thanks.
J
Trigger:
/****** Object: DdlTrigger [MonitorDBChanges] Script Date: 07/24/2007 11:02:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [MonitorDBChanges]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
declare @EventType varchar(100)
declare @SchemaName varchar(100)
declare @ObjectName varchar(100)
declare @ObjectType varchar(100)
declare @Script varchar(2000)
SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@Script = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
-- Is the default schema used
if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p
on u.uid = p.principal_id where u.name = CURRENT_USER
insert into MonitorDBChanges
select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@Script
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [MonitorDBChanges] ON DATABASE
*******************************************************************************
Error Msg:
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)
View 5 Replies
View Related
Sep 26, 2007
Good morning,
I'm stumped on this trigger error and I'm hoping someone can help. Here's the background:
BACKGROUND
Running SQLServer2005 on WinServer 2003 RC2. We use a SQL-based business application that has it's own alert system that uses database mail sucessfully, meaning the built-in emailing functions of the application work and can communicate with the database mail profile and send mail without error.
PROBLEM
I'm trying to write a trigger that will automatically send out an email alert after certain actions are performed in the business application. When I enable the trigger and it tries to run it fails with the following error:
SQLDBCode: 229Alerts error: SQLSTATE = 4200Microsoft OLE DB Provider for SQL ServerEXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'...
TROUBLESHOOTING
I've seen several articles about DatabaseMailUserRole permissions and have made sure that all users, admin and even guest (for testing) are members of this role. Other than that our setup is pretty vanilla so I'm not sure what else to do.
We only have one large dbase for the app and the fact that the internal emailing fuction works makes me think that the permissions for the msdb are already correct (although I could be wrong)
This problem is driving me crazy so I thank you in advance for any suggestions!
View 3 Replies
View Related
Dec 14, 2006
I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)
Error messages:
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549
Any thoughts or direction appreciated
Richard
View 4 Replies
View Related
Oct 9, 2007
When creating a replication publisher in the live server enviroment, the repicaltion displays an error saying
Could not obatin information about Windows NT group/user '........', error code 0x6e. (Microsoft SQL Server, Error: 15404)
When looking at the replication server, the error report says that the user is a System administrator on the Live Server but they do not exist on the replication server.
Why would this cause an error?
I appreciate your answer
Thanks
JC
View 6 Replies
View Related
