CODEWITHSUNDEEP
sql-servertriggers
envyM6
envyM6

Reputation: 1237

Trigger not triggering

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"

Upvotes: 1

Views: 95

Answers (2)

StuartLC
StuartLC

Reputation: 107237

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

Upvotes: 1

user3541575
user3541575

Reputation: 48

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.

Upvotes: 0

Related Questions