[dba-SQLServer] Trigger syntax

Hi Mark

The code below was the code generated by the SQL template which is created when you add a new trigger.
As you know, when you run the SQL containing "CREATE ...", the trigger itself containing "ALTER ..." is created.
A trigger for Insert already existed. It is named [dbo].[T_VariantsTable_ITrig].
That's why I chose the name [dbo].[T_VariantsTable_UTrig] for my Update trigger.

However, this morning - after my workstation had been closed down for the weekend - the issue has vanished. So, for some reason, the new trigger hadn't been saved in full - or what? I really did logout and login again several times and refreshed "everything". But no reboot. Is that really needed?

/gustav


>>> marklbreen at gmail.com 11-06-12 10:33 >>>
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