CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
alextc
alextc

Reputation: 3515

How to create the trigger for the table in SQL Server 2008

I have the following table:

CREATE TABLE [RTS].[MFB]
(
    [record_id] [int] IDENTITY(1,1) NOT NULL,
    [marker_id] [nvarchar](50) NULL,
    [lat] [numeric](38, 8) NULL,
    [lng] [numeric](38, 8) NULL,
    [address] [nvarchar](512) NULL,
    [hash] [smallint] NULL,
    [updated] [datetime] NULL,
    [first_created_date] [datetime] NULL,

    CONSTRAINT [PK_MFB_1] 
        PRIMARY KEY CLUSTERED ([record_id] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

where the "record_id" is the primary key.

I need to create a trigger after the INSERT operation.

The conditions are:

  1. If the marker_id column is new, INSERT the record to the table and set the hash column to 0;
  2. If the marker_id already exists, UPDATE the existing record by setting the new updated column;
  3. If both the marker_id already exists and any of the "lat", "lng" and "address" has been changed, UPDATE the existing record by setting the new "lat", "lng" and/or "address" and also setting "hash" to "1".

Basically, the MFB table should not have duplicated marker_id.

How can I achieve this by a setting up a trigger? Thanks!

Upvotes: 0

Views: 65

Answers (2)

Murat Vezir
Murat Vezir

Reputation: 216

Rafal is right but you can make a cursor for bulk insert and update but i cant promise for performance it should be like this

CREATE TRIGGER DBO.MFBTRG 
   ON  DBO.MFB 
   INSTEAD OF INSERT,UPDATE
AS 
BEGIN
DECLARE @marker_id NVARCHAR(50)
DECLARE @lat NUMERIC(38,8)
DECLARE @lng NUMERIC(38,8)
DECLARE @address NVARCHAR(512)
DECLARE @hash SMALLINT
DECLARE @updated DATETIME
DECLARE @first_created_date DATETIME
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE MFBINS CURSOR  FAST_FORWARD FOR  Select [marker_id],[lat],[lng],[address],[hash],[updated],[first_created_date] FROM INSERTED
OPEN MFBINS 
FETCH NEXT FROM MFBINS INTO @marker_id,@lat,@lng,@address,@hash,@updated,@first_created_date
        WHILE (@@FETCH_STATUS=0)
        BEGIN

IF NOT EXISTS (SELECT [marker_id] FROM MFB WHERE [marker_id]= @marker_id)
BEGIN
INSERT INTO [dbo].[MFB] ([marker_id],[lat],[lng],[address],[hash],[updated],[first_created_date])
           VALUES (@marker_id,@lat,@lng,@address,@hash,@updated,@first_created_date)
END
ELSE
BEGIN 
UPDATE MFB SET [updated]=@updated WHERE [marker_id]=@marker_id
END 


    -- Insert statements for trigger here
FETCH NEXT FROM MFBINS INTO @marker_id,@lat,@lng,@address,@hash,@updated,@first_created_date
END
CLOSE MFBINS
DEALLOCATE MFBINS
END
GO

and you can use to detect which column is update on update trigger with 

IF UPDATE(COLUMN_NAME)
BEGIN
UPDATE LOGİC
END

Upvotes: 1

user170442
user170442

Reputation:

If you really want to do it this way you would have to create INSTEAD OF INSERT trigger - but beware it is going to be slow as you wouldn't be able to benefit from bulk insert.

Alternatively you could use MERGE statement and perform your INSERT/UPDATE scenario there.

Upvotes: 1

Related Questions