[dba-SQLServer] Trigger syntax

Hello Gustav

I just copied your code into SSMS, change the two table names and it
worked.  It created a trigger on a db here locally on my machine.

The only thing I had to do was change the Alter to a Create.  Would that be
your problem?

If not, the only other thought that comes to mind is the PPT database the
one with the table to hold the trigger.  IOW, does the db and table exist?

Sorry I could not help more,

Mark


On 8 June 2012 14:08, Gustav Brock <gustav at cactus.dk> wrote:

> 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>