August 25, 2006 at 9:46 am
Need advice on how to develop a TRIGGER to accomodate the following ---- Our Architect wants a TRIGGER defined to UPDATE TableA's ChangeDate and ChangeUser columns. This part is simple:
CREATE TRIGGER trgu_TableA ON TableA AFTER UPDATE AS
BEGIN
UPDATE TableA
SET ChangeDate = (SELECT GETDATE()),
ChangeUser = (SELECT SUSER_SNAME())
FROM TableA A, INSERTED I WHERE A.ID = I.ID
END
The intricate part is this: The application provides 2 variables: @ChangeDate, @ChangeUser
IF either of these variables are populated in the application (VB.NET) then we need to use their values to update the columns -- ELSE default to the above GETDATE & SUSER_SNAME values. (this way, all Query Analyzer UPDATES would default to GETDATE & SUSER_SNAME values)
August 25, 2006 at 10:24 am
>>The application provides 2 variables: @ChangeDate, @ChangeUser
The application is VB.Net. Those are T-SQL variables. How does the application "provide" them ? As parameters to a stored proc ? And if so, what does the stored proc do with them ?
August 25, 2006 at 10:29 am
The VB.NET application passes the variable values to a STORED PROC. The proc performs the UPDATEs.
August 25, 2006 at 11:10 am
So, the UPDATE may already have set the ChangeDate and ChangeUser column values ? And you only want the trigger code to fire if this *didn't* happen, correct ?
In this case, if the VB.Net app provided the column values, you can check that by comparing inserted with deleted virtual tables. If inserted & deleted are different for ChangeDate or ChangeUser, you know you don't have to do anything.
UPDATE TableA
SET ChangeDate = GETDATE(),
ChangeUser = SUSER_SNAME()
FROM TableA As A
INNER JOIN INSERTED As I ON (A.ID = I.ID)
INNER JOIN DELETED As D ON (A.ID = D.ID)
WHERE d.ChangeDate = i.ChangeDate
^^ Be careful with NULLs in the WHERE. We don't know if ChangeDate is nullable, and you'll have to handle that situation if the column can be NULL.
August 25, 2006 at 2:14 pm
Almost there... Using your example join of the Virtual tables, I've prepped sample DDL & SQL for use here. In the following SAMPLE, TEST 1 works fine but TEST 2 does not.... I'm looking for the trigger to update the ChangeUser, ChangeDate, or both. (eg. If neither value is supplied by an UPDATE statement, both should be updated by the trigger. If the ChangeDate is supplied by an UPDATE, the ChangeUser should be updated, If the ChangeUser is supplied by the UPDATE - the ChangeDate should be updated)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TRASH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TRASH]
GO
CREATE TABLE [dbo].[TRASH](
[ID] [int] NOT NULL,
[col2] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChangeDate] [datetime] NOT NULL,
[ChangeUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_TRASH] PRIMARY KEY CLUSTERED
([ID] ASC) ON [PRIMARY]) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].trgu_TRASH')
AND OBJECTPROPERTY(id,N'IsTrigger') = 1)
DROP TRIGGER trgu_TRASH
GO
CREATE TRIGGER trgu_TRASH ON TRASH AFTER UPDATE
AS
UPDATE TRASH
SET ChangeDate = GETDATE(),
ChangeUser = SUSER_SNAME()
FROM TRASH As T
INNER JOIN INSERTED As I ON (T.ID = I.ID)
INNER JOIN DELETED As D ON (T.ID = D.ID)
WHERE d.ChangeDate = i.ChangeDate
AND d.ChangeUser = i.ChangeUser;
GO
/*
DELETE FROM TRASH
GO
INSERT INTO TRASH (ID, col2, ChangeDate, ChangeUser)
VALUES (1, 'Johnny', '1997-03-04 01:00:00.000', 'JT')
INSERT INTO TRASH (ID, col2, ChangeDate, ChangeUser)
VALUES (2, 'William', '1998-12-08 02:00:00.000', 'WT')
INSERT INTO TRASH (ID, col2, ChangeDate, ChangeUser)
VALUES (3, 'Elizabeth', '2000-05-12 03:00:00.000', 'ET')
SELECT * FROM YourDBName..TRASH
*/
--TEST 1: Update ONLY col2
UPDATE TRASH SET col2 = 'MIKE' WHERE ID = 1
SELECT * FROM YourDBName..TRASH
--TEST 2: Update ONLY ChangeDate
UPDATE TRASH SET ChangeDate = '2006-05-05 05:05:05.003' WHERE ID = 1
SELECT * FROM YourDBName..TRASH
--TEST 2: Update ONLY ChangeUser
UPDATE TRASH SET ChangeUser = 'Flintstone' WHERE ID = 1
SELECT * FROM YourDBName..TRASH
August 25, 2006 at 2:24 pm
>>I'm looking for the trigger to update the ChangeUser, ChangeDate, or both.
That changes things.
UPDATE TRASH
SET ChangeDate =
CASE WHEN d.ChangeDate <> i.ChangeDate THEN i.ChangeDate
ELSE GETDATE()
END,
CASE WHEN d.ChangeUser <> i.ChangeUser THEN i.ChangeUser
ELSE SUSER_SNAME()
END
FROM TRASH As T
INNER JOIN INSERTED As I ON (T.ID = I.ID)
INNER JOIN DELETED As D ON (T.ID = D.ID)
August 25, 2006 at 2:34 pm
PW -- pure genius.... If I were your boss I'd give you a scholarly bonus..
thanks -- that worked.
August 28, 2006 at 7:04 am
Just ran into a slight glich ---
When the same person updates the row 2 times in succession, won't the 2nd update use SUSER_NAME? We'd need it to continue to use the actual ChangeUser value supplied by the app?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply