April 10, 2015 at 1:29 pm
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,
[groupName] [nvarchar](max) NULL,
[ruleCollectionJSON] [nvarchar](max) NULL,
[isActive] [bit] NOT NULL,
[ruleType] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,
[groupName] [nvarchar](max) NULL,
[ruleCollectionJSON] [nvarchar](max) NULL,
[isActive] [bit] NOT NULL,
[ruleType] [int] NOT NULL,
[updateDate] [date] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
AFTER UPDATE
AS
IF UPDATE (ruleCollectionJSON)
BEGIN
INSERT INTO
[dbo].[ActiveDirectoryGroupRulesArchive] (id, groupName, ruleCollectionJSON, isActive, ruleType, updateDate)
select id, groupName, ruleCollectionJSON, isActive, ruleType, getdate()
from [ActiveDirectoryGroupRules];
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
Any and all assistance would be greatly appreciated, what did I miss? Should be simple right? I can't sort by uniqueidentifier in descending as those can be random. Thanks in advance for the help anyone!!
April 10, 2015 at 1:38 pm
you're not joining to the Inserted or Deleted virtual tables, so your result set is not getting filtered. Once you do that, only the affected records will be updated.
April 10, 2015 at 1:39 pm
How would I do that?
April 10, 2015 at 2:06 pm
Greg.Jackson (4/10/2015)
How would I do that?
You don't want to get the "last row" modified as you stated. You want to get ALL rows currently being modified. Remember that triggers in sql server are not called for each row, they are called for the entire operation.
Here is one reference that you may find useful.
https://msdn.microsoft.com/en-us/library/ms191300.aspx
There are literally thousands of articles about this, just hit your old friend google and you will them. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2015 at 2:41 pm
Can anyone provide specific code?
April 10, 2015 at 2:45 pm
Based on the tables above, in theory would this work:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
AFTER UPDATE
AS
IF UPDATE (ruleCollectionJSON)
BEGIN
INSERT INTO
[dbo].[ABCD] (id, groupName, ruleCollectionJSON, isActive, ruleType, updateDate)
select TOP 1 id, groupName, ruleCollectionJSON, isActive, ruleType, getdate()
from ABC TAB1, ABCD TAB2 where getdate() > (Select updateDate from TAB2);
END;
April 10, 2015 at 3:10 pm
For update you need to use deleted or inserted table.
insert into abcd select *, getdate() from deleted if you want values before update.
April 10, 2015 at 3:13 pm
Thank you!
Just found that about 10 minutes ago. I didn't realize I could pull it from deleted.
Thanks everyone. I was so close on my first attempt. I just wanted to pull it from deleted and not my original table.
April 10, 2015 at 3:21 pm
As it has been noticed, on a trigger you should use inserted and deleted virtual tables.
I'd use a different approach to be sure that the value on the column changed.
CREATE TRIGGER [dbo].[ActiveDirectoryGroupRules_trigger] ON [dbo].[ActiveDirectoryGroupRules]
AFTER UPDATE
AS
INSERT INTO [dbo].[ActiveDirectoryGroupRulesArchive]
(
id,
groupName,
ruleCollectionJSON,
isActive,
ruleType,
updateDate
)
SELECT d.id,
d.groupName,
d.ruleCollectionJSON,
d.isActive,
d.ruleType,
getdate()
FROM Deleted d
JOIN Inserted i ON i.id = d.id
WHERE i.ruleCollectionJSON <> d.ruleCollectionJSON;
GO
April 10, 2015 at 3:31 pm
So the deleted virtual tables are similar to the oracle redo/undo.
Correct?!
April 10, 2015 at 4:05 pm
Not exactly. They're more like :old and :new
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply