Trigger not triggering

Question

I've got two tables as per diagram below

enter image description here

here is sql script for USERS TABLES

USE [NewUser]
GO

/****** Object:  Table [dbo].[USERS]    Script Date: 15/12/2014 18:26:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[USERS](
    [FirstName] [varchar](50) NULL,
    [SureName] [varchar](50) NULL,
    [DOB] [date] NULL,
    [USID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED 
(
    [USID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

And for ADDRESSES TABLE

USE [NewUser]
GO

/****** Object:  Table [dbo].[ADDRESSES]    Script Date: 15/12/2014 18:26:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ADDRESSES](
    [AID] [int] IDENTITY(1,1) NOT NULL,
    [USID] [numeric](18, 0) NULL,
    [AddressLine1] [varchar](50) NULL,
    [AddressLine2] [varchar](50) NULL,
    [PostCode] [varchar](50) NULL,
 CONSTRAINT [PK_ADDRESSES] PRIMARY KEY CLUSTERED 
(
    [AID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ADDRESSES]  WITH CHECK ADD  CONSTRAINT [FK_ADDRESSES_USERS]
FOREIGN     KEY([USID])
REFERENCES [dbo].[USERS] ([USID])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[ADDRESSES] CHECK CONSTRAINT [FK_ADDRESSES_USERS]
GO

After that I have created a TRIGGER using

USE [NewUser]
GO
/****** Object:  Trigger [dbo].[autoupdate]    Script Date: 15/12/2014 18:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[autoupdate]
    ON [dbo].[USERS]
    AFTER INSERT, DELETE, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TEMPID INT
    SET @TEMPID = (SELECT USID FROM INSERTED)
    UPDATE ADDRESSES
    SET USID = @TEMPID
END

The purpose of the TRIGGER is to insert UID automatically on the second table as soon as a new row is added or updated in first table.

But its not doing what I was expecting it to do.

Screenshots

USERS TABLE

enter image description here

ADDRESSES TABLE

enter image description here

USID from table USERS and AID from table ADDRESSES both has "Is Identity Set to YESS"

the aim was to update `USID` on second table (ADDRESSES) automatically as I insert row into first table (USERS). But second table is unaffected. Nothing happens! — envyM6, Dec 15 '14 at 18:47
Your trigger has a major flaw. It assumes there will only every single row operations. If you use this code it not only can't handle multiple row operations it will update the ENTIRE Addresses table every single time. — Sean Lange, Dec 15 '14 at 18:49
rene It doesnt... let me add the screen shots. @SeanLange I dont mind as long as it works — envyM6, Dec 15 '14 at 18:51

score 1 · Accepted Answer · edited Dec 15 '14 at 19:07

1

I'm assuming this is SQL Server, not MySql - please remove the conflicting RDBMS tag.

With triggers, you'll need to handle set data - the INSERTED and DELETED pseudocolumns are tables, not single rows.

Also, assuming that you aren't going to be updating the PK [USID] on Users, you won't need to handle an UPDATE in the trigger, just INSERT and DELETE, and it looks like you've already decided to handle deletes on user with ON DELETE SET NULL, which will set the USID foreign key to NULL in Addresses.

So this leaves INSERT - I'm guessing here you only want to insert an empty address for new users?:

ALTER TRIGGER [dbo].[autoupdate]
    ON [dbo].[USERS]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO ADDRESSES(USID)
        SELECT USID FROM INSERTED i
        WHERE NOT EXISTS (SELECT 1 FROM ADDRESSES a WHERE a.USID = i.USID)
        AND i.USID IS NOT NULL;

END

edited Dec 15 '14 at 19:07

marc_s

732,580
175
1,330
1,459

answered Dec 15 '14 at 18:59

StuartLC

104,537
17
209
285

can you explain your code a bit more please? I did not understand this block... SELECT USID FROM INSERTED i WHERE NOT EXISTS (SELECT 1 FROM ADDRESSES a WHERE a.USID = i.USID) AND i.USID IS NOT NULL; – envyM6 Dec 16 '14 at 09:05
We basically just want to check that the user id from the newly inserted address row doesn't already exist in the addresses table. This is probably more relevant to an UPDATE trigger in this case. If you don't want this behaviour, you can revert to just inserting directly into addresses? – StuartLC Dec 16 '14 at 09:08
I meant What is `SELECT 1` AND `i` here? – envyM6 Dec 16 '14 at 09:10
`i` is just an alias for the INSERTED pseudotable, and `SELECT 1` is commonly used with `EXISTS` - it doesn't really matter what you put after the select when [checking for existence](http://stackoverflow.com/questions/7039938/what-does-select-1-from-do) – StuartLC Dec 16 '14 at 09:15

score 0 · Answer 2 · answered Dec 15 '14 at 18:58

0

In order for your trigger to do anything the way you've designed it, you need to have records already in the Addresses table. Once a record exists, it's USID will be updated when a record is updated or inserted in the Users table.

answered Dec 15 '14 at 18:58

user3541575

48
4

Trigger not triggering

2 Answers2