[dba-SQLServer] Trigger syntax

Gustav Brock gustav at cactus.dk
Fri Jun 8 08:08:43 CDT 2012


Hi all

I'm new to triggers but I've managed to create this which works as intended:

USE [PPT]
GO
/****** Object:  Trigger [dbo].[T_VariantsTable_UTrig]    Script Date: 06/08/2012 14:45:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:		Gustav Brock
-- Create date: 2012-06-08
-- Description:	Update field Created to time of update of record
-- =============================================================
ALTER TRIGGER [dbo].[T_VariantsTable_UTrig] 
   ON [dbo].[VariantsTable] 
   FOR UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @id INT
    DECLARE @now DATETIME
    
    -- Retrieve ID of changed record.
    SELECT @id = (SELECT ID FROM deleted) 
    -- Get current time without milliseconds.
    SELECT @now = DATEADD(SECOND, DATEDIFF(SECOND, '20000101', GETDATE()), '20000101')
	
    UPDATE VariantsTable 
    SET Changed = @now 
    WHERE ID = @id
END

However, when I open this in SMMS, this line has the last part red underlined:

ALTER TRIGGER [dbo].[T_VariantsTable_UTrig] 

stating: Invalid object name 'dbo.T_VariantsTable_UTrig'

Why is that? 
I have another trigger created by the Access upsize wizard with similar code:

ALTER TRIGGER [dbo].[T_VariantsTable_ITrig] 

and nothing is underlined here.

/gustav



More information about the dba-SQLServer mailing list