[dba-SQLServer] Trigger syntax

Hi Jim

Yes, nothing fancy. And besides, the trigger works fine.

I guess somehow the new trigger, though saved and working, was not recognised by its name as a member of the collection by the intellisense until a reboot had happened.

/gustav


>>> accessd at shaw.ca 11-06-12 19:14 >>>
Hi Gustav:

Have set up all the permissions right? 

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Monday, June 11, 2012 4:10 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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