Tuesday, August 27, 2013

How to check if column was modified using trigger in sql server

CREATE TRIGGER dbo.haschanged --Any Trigger name
ON [table]
after UPDATE
AS
  BEGIN
      SET nocount ON;

      DECLARE @Old VARCHAR(100),
              @New VARCHAR(100)

      SELECT @New = i.[col]
      FROM   inserted AS i

      SELECT @Old = d.[col]
      FROM   deleted AS d

      IF @New <> @Old
        BEGIN
            --Field was changed
            
        END
      ELSE
        BEGIN
            --Field was not changed
           
        END
  END 

OR
CREATE TRIGGER dbo.haschanged --Any Trigger name
ON [table]
after UPDATE
AS
  BEGIN
      IF UPDATE (col)
        BEGIN
            --Field was changed
            
        END
      ELSE
        BEGIN
            --Field was not changed
           
        END
  END 

Insert / Update image into SQL Server database using query (not front end application)

Using Insert

INSERT INTO [table]
            (COLUMN)
SELECT *
FROM   OPENROWSET(BULK N'Path to file', single_blob) AS [File] 


Using Update

UPDATE [table]
SET    [column] = (SELECT *
                   FROM   OPENROWSET(BULK N'Path to file', single_blob) AS
                          [File])
--Conditions if any