July 25, 2008 at 5:50 am
Hi all,
I want to copy/update/delete data from one identical table to another table in a different database (same server).
The INSERT trigger below keeps throwing errors, what am I doing wrong?
--------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Insert_tblSymbolTrackingOld] ON [dbo].[tblSymbolTrackingOld]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
SET IDENTITY_INSERT [dbo].[tblSymbolTracking] ON;
GO
INSERT INTO [dbo].[tblSymbolTracking] SELECT * FROM [dbo].[tblSymbolTrackingOld]
END
------------------------------------------------------------
In this example for testing I have made a temporary table to test the triggers [dbo].[tblSymbolTrackingOld].
Ideally I do not need to insert the IDENTITY column as this can get re-generated when it gets inserted.
Thank you.
Mike
July 25, 2008 at 5:59 am
what exactly is the error that you are getting?
July 25, 2008 at 6:04 am
If I remove this portion:
SET IDENTITY_INSERT ON [dbo].[tblSymbolTracking] ON;
GO
I get a warning:
A explicit value for the identity column in table ... can only be specified whena column is used and IDENTITY_INSERT is ON.
Hence the reason for adding that portion of code.
Now I get ...
Incorrect syntax near ';' and Incorrect syntax near 'END'
Thanks
July 25, 2008 at 6:15 am
"GO" is a batch spearator handled by the client. It is the equivalant of highlighting everything before the GO, executing the query, and then highlighting everything after the GO and executing the query.
You cannot use it within a trigger, procedure, or function. The create statement ends at the GO. ";" is also a batch separator, but it is server-side. I think it will work within a trigger (although I have never tried it).
If your table is being populated solely by this trigger, you should just remove the IDENTITY property of the destination table. As it is, your trigger has the overhead of turning on IDENTITY INSERT and it is probably not necessary.
July 25, 2008 at 6:30 am
Why are you setting the Identity Insert On in the trigger.
July 25, 2008 at 6:37 am
SELECT * FROM [dbo].[tblSymbolTrackingOld]
will insert all records of tblSymbolTrackingOld in tblSymbolTracking every time you insert a record in tblSymbolTrackingOld.
I am not sure if that is what you want.
July 25, 2008 at 7:03 am
If I do the following it works perfect on an INSERT.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Insert_tblSymbolTrackingOld] ON [dbo].[tblSymbolTrackingOld]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
SET IDENTITY_INSERT [dbname].[dbo].[tblSymbolTracking] ON;
DECLARE @symbol_id int;
DECLARE @company nvarchar(10);
DECLARE @order_number nvarchar(50);
DECLARE @true_order_number nvarchar(50);
DECLARE @carrier nvarchar(10);
DECLARE @actual_carrier nvarchar(10);
DECLARE @tracking_number nvarchar(50);
DECLARE @order_watch int;
DECLARE @entry_datetime datetime;
DECLARE @entry_user nvarchar(50);
DECLARE @record_datetime datetime;
DECLARE @record_user nvarchar(50);
SELECT @symbol_id = symbol_id FROM Inserted;
SELECT @company = company FROM Inserted;
SELECT @order_number = order_number FROM Inserted;
SELECT @true_order_number = true_order_number FROM Inserted;
SELECT @carrier = carrier FROM Inserted;
SELECT @actual_carrier = actual_carrier FROM Inserted;
SELECT @tracking_number = tracking_number FROM Inserted;
SELECT @order_watch = order_watch FROM Inserted;
SELECT @entry_datetime = entry_datetime FROM Inserted;
SELECT @entry_user = entry_user FROM Inserted;
SELECT @record_datetime = record_datetime FROM Inserted;
SELECT @record_user = record_user FROM Inserted;
INSERT INTO [dbname].[dbo].[tblSymbolTracking] (symbol_id, company, order_number, true_order_number, carrier, actual_carrier, tracking_number, order_watch, entry_datetime, entry_user, record_datetime, record_user) VALUES (@symbol_id, @company, @order_number, @true_order_number, @carrier, @actual_carrier, @tracking_number, @order_watch, @entry_datetime, @entry_user, @record_datetime, @record_user)
END
Thanks.
Mike
July 25, 2008 at 7:13 am
mdouglass (7/25/2008)
If I do the following it works perfect on an INSERT.SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Insert_tblSymbolTrackingOld] ON [dbo].[tblSymbolTrackingOld]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
SET IDENTITY_INSERT [dbname].[dbo].[tblSymbolTracking] ON;
DECLARE @symbol_id int;
DECLARE @company nvarchar(10);
DECLARE @order_number nvarchar(50);
DECLARE @true_order_number nvarchar(50);
DECLARE @carrier nvarchar(10);
DECLARE @actual_carrier nvarchar(10);
DECLARE @tracking_number nvarchar(50);
DECLARE @order_watch int;
DECLARE @entry_datetime datetime;
DECLARE @entry_user nvarchar(50);
DECLARE @record_datetime datetime;
DECLARE @record_user nvarchar(50);
SELECT @symbol_id = symbol_id FROM Inserted;
SELECT @company = company FROM Inserted;
SELECT @order_number = order_number FROM Inserted;
SELECT @true_order_number = true_order_number FROM Inserted;
SELECT @carrier = carrier FROM Inserted;
SELECT @actual_carrier = actual_carrier FROM Inserted;
SELECT @tracking_number = tracking_number FROM Inserted;
SELECT @order_watch = order_watch FROM Inserted;
SELECT @entry_datetime = entry_datetime FROM Inserted;
SELECT @entry_user = entry_user FROM Inserted;
SELECT @record_datetime = record_datetime FROM Inserted;
SELECT @record_user = record_user FROM Inserted;
INSERT INTO [dbname].[dbo].[tblSymbolTracking] (symbol_id, company, order_number, true_order_number, carrier, actual_carrier, tracking_number, order_watch, entry_datetime, entry_user, record_datetime, record_user) VALUES (@symbol_id, @company, @order_number, @true_order_number, @carrier, @actual_carrier, @tracking_number, @order_watch, @entry_datetime, @entry_user, @record_datetime, @record_user)
END
Instead of declaring variables can you use
INSERT INTO [dbname].[dbo].[tblSymbolTracking]
SELECT symbol_id, company, order_number, true_order_number, carrier, actual_carrier, tracking_number, order_watch, entry_datetime, entry_user, record_datetime, record_user FROM inserted
Rosh
July 25, 2008 at 7:34 am
Hi all,
OK, sorted it out!
You can do the following for INSERT / UPDATE & DELETE triggers to maintain identical data between two tables!
*** INSERT TRIGGER ***
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Insert_tblSymbolTrackingOld] ON [dbo].[tblSymbolTrackingOld]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
SET IDENTITY_INSERT [Omega_OH].[dbo].[tblSymbolTracking] ON;
INSERT INTO [Omega_OH].[dbo].[tblSymbolTracking] (symbol_id, company, order_number, true_order_number, carrier, actual_carrier, tracking_number, order_watch, entry_datetime, entry_user, record_datetime, record_user) SELECT symbol_id, company, order_number, true_order_number, carrier, actual_carrier, tracking_number, order_watch, entry_datetime, entry_user, record_datetime, record_user FROM Inserted
END
*** UPDATE TRIGGER ***
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Update_tblSymbolTrackingOld] ON [dbo].[tblSymbolTrackingOld]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Omega_OH].[dbo].[tblSymbolTracking] SET
company = I.company,
order_number = I.order_number,
true_order_number = I.true_order_number,
carrier = I.carrier,
actual_carrier = I.actual_carrier,
tracking_number = I.tracking_number,
order_watch = I.order_watch,
entry_datetime = I.entry_datetime,
entry_user = I.entry_user,
record_datetime = I.record_datetime,
record_user = I.record_user
FROM [dbo].[tblSymbolTracking] AS T INNER JOIN Inserted AS I
ON T.symbol_id = I.symbol_id
END
*** DELETE TRIGGER ***
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Delete_tblSymbolTrackingOld] ON [dbo].[tblSymbolTrackingOld]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @symbol_id int;
SELECT @symbol_id = symbol_id FROM Deleted;
DELETE [Omega_OH].[dbo].[tblSymbolTracking] WHERE ([Omega_OH].[dbo].[tblSymbolTracking].symbol_id = @symbol_id)
END
Thank you all.
Mike
😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply