Creating a SQL Server trigger error

Question

I am getting an error and can't figure out what I am doing wrong. I am creating a trigger on a table to archive this table daily. The table is truncated and new data is inserted in the morning every day. I want to archive it once this happens.

This is my code to create the trigger:

IF OBJECT_ID('trg_archive_inventory_table') IS NOT NULL
   DROP TRIGGER trg_archive_inventory_table
GO

CREATE TRIGGER trg_archive_inventory_table 
ON inventory 
AFTER INSERT
AS 
BEGIN
   SET NOCOUNT ON;

   /* delete data from one year ago */
   DELETE FROM [cadc].[dbo].[inventory_archive] 
   WHERE entry_date = CAST((DATEADD(yy, -1, GetDate())) as DATE);

   /* insert todays inventory */
   INSERT INTO [cadc].[dbo].[inventory_archive] (sku, tag_id , load_id, batch, status, qc_status, location, eaches, units, cartons, r_date, e_date, zone_1, zone_2, loc_type, loc_pick, mod_time, receiver_no, mfg_code, each_unit, unit_carton)
       SELECT 
           sku, tag_id , load_id, batch, status, qc_status, location, eaches, 
           units, cartons, r_date, e_date, zone_1, zone_2, loc_type, loc_pick, 
           mod_time, receiver_no, mfg_code, each_unit, unit_carton 
       FROM 
           [cadc].[dbo].[inventory];
END

I keep getting the following error:

'CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.

I have also tried prefixing the inventory table with [cadc].[dbo].[inventory] but I receive the same error.

Googling has help understand the problem.

Any help would be great.

Just a word of caution: a trigger is fired whenever that operation happens - you have no control over when and how many times it's fired. Therefore, it should be as lean as possible and it should not do any extensive operations - like archiving. Something like this - archive this table daily - sounds much more suitable to a scheduled SQL Server Agent job that can be launched once a day (or night) to do its task — marc_s
– marc_s, Commented Nov 28, 2014 at 17:22
This should be an nightly process, schedule a window job for this — HaveNoDisplayName
– HaveNoDisplayName, Commented Nov 28, 2014 at 17:25
As I said - this should not be a trigger to begin with. But the error message seems pretty clear - you cannot use a three-part name for a table - [cadc].[dbo].[inventory] - you must restrict yourself to a two-part name (schema + object name) only - [dbo].[inventory] — marc_s
– marc_s, Commented Nov 28, 2014 at 17:27
@marc_s OK I don't have access to set it up as a job with SQL Agent job which is why I used a trigger. I know for sure that the job to import data to my inventory table runs exactly once a day. I have a check to see if the table has been archived already so I know it won't be done twice in one day. I understand why I would want to separate the 2 processes and will talk to my DB admin. Thanks — ninedoors
– ninedoors, Commented Nov 28, 2014 at 17:47

ninedoors · Accepted Answer · 2014-11-28 17:52:01Z

1

I needed to set the database above the trigger

IF OBJECT_ID('trg_archive_inventory_table') IS NOT NULL
DROP TRIGGER trg_archive_inventory_table
GO

USE cadc;
GO

CREATE TRIGGER trg_archive_inventory_table ON  inventory AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @lastrun date;

/* make sure the inventory table was updated this morning*/ 
SELECT @lastrun = MAX(entry_date) FROM [cadc].[dbo].[inventory_archive];

IF (@lastrun < CAST(GETDATE() as date))
BEGIN
    /* delete data from one year ago */
    DELETE FROM [cadc].[dbo].[inventory_archive] WHERE entry_date = CAST((DATEADD(yy, -1, GetDate())) as DATE);

    /* insert todays inventory */
    INSERT INTO [cadc].[dbo].[inventory_archive] (sku, tag_id , load_id, batch, status, qc_status, location, eaches, units, cartons, r_date, e_date, zone_1, zone_2, loc_type, loc_pick, mod_time, receiver_no, mfg_code, each_unit, unit_carton)
        SELECT sku, tag_id , load_id, batch, status, qc_status, location, eaches, units, cartons, r_date, e_date, zone_1, zone_2, loc_type, loc_pick, mod_time, receiver_no, mfg_code, each_unit, unit_carton 
        FROM [cadc].[dbo].[inventory];
END
END

As pointed out above by @marc_s I am going to try and move this to a job that runs.

answered Nov 28, 2014 at 17:52

ninedoors

1254 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andriy M Over a year ago

I'd move USE cadc to the beginning of the script. Otherwise if the trigger already exists but you start the script in a different database, it will fail to execute the CREATE TRIGGER statement.

Collectives™ on Stack Overflow

Creating a SQL Server trigger error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related