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

Question

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:

If the marker_id column is new, INSERT the record to the table and set the hash column to 0;
If the marker_id already exists, UPDATE the existing record by setting the new updated column;
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!

You can't handle whether to insert or update through trigger because it will trigger after inserting, updating & deleting. You can do it for another table (good example is LOG table) based on the event on this table. — Shushil Bohara
– Shushil Bohara, Commented Mar 2, 2017 at 6:21
I suggest you do not use a trigger. I suggest you create a unique index on the marker_id column and make sure changes to this table only occur through a stored procedure that enforces these business rules. Regardless, you need to show what you've already tried - no one will write your code for you. — Nick.Mc
– Nick.Mc, Commented Mar 2, 2017 at 6:36

Murat Vezir · Accepted Answer · 2017-03-02 19:42:56Z

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

user170442 · Accepted Answer · 2017-03-02 06:40:11Z

1

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.

answered Mar 2, 2017 at 6:40

user170442

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related