May 14, 2008 at 8:36 am
I'm really new at Triggers, I tried to do some research on my own, but was confused so I was hoping someone here might be able to help me. What i'm looking to do is before a change is made to a table I do a compare to see what 2 values have changed and if they have changed then I write them to a new table. What i'm trying to accomplish is more like a transaction history for changes only. Any assistance would help. this is what i started with but I have so many errors i think i am way off.
CREATE TRIGGER [dbo].[trAgrPkeyHistory] BEFORE INSERT ON dbo.TicketCallMain
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE ChangeAgreement SMALLINT;
DECLARE CurrentAgreement SMALLINT;
DECLARE NextHistoryPkey SMALLINT;
SET ChangeAgreement = new_row.AgrPkey;
SET CurrentAgreement= Inserted.AgrPKey;
IF ChangeAgreement CurrentAgreement THEN
INSERT INTO TicketCallMainModifiedHistory
SELECT Inserted.TcaPKey, Inserted.AgrPKey, Inserted.AloPKey, Inserted.TcaType, Inserted.TcaStatus, Inserted.TcaModifiedDate, Inserted.TcaEngineer, Inserted.TcaOffice
FROM TicketCallMain
INNER JOIN Inserted ON TicketCallMain.TcaPKey= Inserted.TcaPKey
SET NextHistoryPkey = (SELECT MAX(ThmPkey) + 1 AS NextPkey FROM TicketCallMainModifiedHistory);
UPDATE TicketCallMainModifiedHistory SET ThmAgrPkey =ChangeAgreement WHERE (ThmPkey = NextHistoryPkey)
END IF;
END
Thanks
Bender
May 14, 2008 at 9:04 am
SQL Server does not have before triggers. In SQL Server you would use an after trigger and then use the virtual inserted and deleted tables. I will post an example later. You are using SQL Server, not DB2?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2008 at 9:06 am
Currently we are using SQL Server Enterprise manager to write most of queries. Thanks for you help!
May 14, 2008 at 9:30 am
Here are 3 options. Option 1 is simpler, but it will create rows even if you do Update TicketCallMain set AgrPKey = AgrPKey, while Options 2 is not complex it is a little more complex, OPtion 3 is a combination of the first 2.
Option 1:
[font="Courier New"]CREATE TRIGGER [dbo].[trAgrPkeyHistory] ON dbo.TicketCallMain
FOR UPDATE -- I think update is what you are looking for as inserting is not a change, but a new record
AS
BEGIN
IF UPDATE(AgrPkey) -- this says if this column is updated then run it
BEGIN
-- the deleted virtual table contains the "before" record
INSERT INTO TicketCallMainModifiedHistory
SELECT
D.TcaPKey,
D.AgrPKey,
D.AloPKey,
D.TcaType,
D.TcaStatus,
D.TcaModifiedDate,
D.TcaEngineer,
D.TcaOffice
FROM
deleted d
END
END
[/font]
Option 2:
[font="Courier New"]CREATE TRIGGER [dbo].[trAgrPkeyHistory] ON dbo.TicketCallMain
FOR UPDATE -- I think update is what you are looking for as inserting is not a change, but a new record
AS
BEGIN
-- this will handle a batch update and only insert records where AgrPKey has changed
INSERT INTO TicketCallMainModifiedHistory
SELECT
D.TcaPKey,
D.AgrPKey,
D.AloPKey,
D.TcaType,
D.TcaStatus,
D.TcaModifiedDate,
D.TcaEngineer,
D.TcaOffice
FROM
inserted I JOIN
deleted d ON
I.TcaPkey = D.TcaPkey AND -- assuming this is the Primary key on TicketCallMain
I.AgrPKey != D.AgrPkey -- this assures that only where this column was updated will change
END
[/font]
Option 3:
[font="Courier New"]CREATE TRIGGER [dbo].[trAgrPkeyHistory] ON dbo.TicketCallMain
FOR UPDATE -- I think update is what you are looking for as inserting is not a change, but a new record
AS
BEGIN
IF UPDATE(AgrPkey) -- this says if the
BEGIN
-- this will handle a batch update and only insert records where AgrPKey has changed
INSERT INTO TicketCallMainModifiedHistory
SELECT
D.TcaPKey,
D.AgrPKey,
D.AloPKey,
D.TcaType,
D.TcaStatus,
D.TcaModifiedDate,
D.TcaEngineer,
D.TcaOffice
FROM
inserted I JOIN
deleted d ON
I.TcaPkey = D.TcaPkey AND -- assuming this is the Primary key on TicketCallMain
I.AgrPKey != D.AgrPkey -- this assures that only where this column was updated will change
END
END
[/font]
This should give you some ideas anyway. You can also lookup INSTEAD OF triggers in BOL(books on line)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2008 at 9:31 am
Jack Corbett (5/14/2008)
SQL Server does not have before triggers. In SQL Server you would use an after trigger and then use the virtual inserted and deleted tables. I will post an example later. You are using SQL Server, not DB2?
Well - they're sometimes referred to as "BEFORE" triggers, since that's where they occur...but the official name is an INSTEAD OF trigger. INSTEAD OF triggers can be set up to make an action happen prior to actual insertion (i.e. a true BEFORE trigger), or to prevent/modify the insertion.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 9:39 am
Matt Miller (5/14/2008)
Jack Corbett (5/14/2008)
SQL Server does not have before triggers. In SQL Server you would use an after trigger and then use the virtual inserted and deleted tables. I will post an example later. You are using SQL Server, not DB2?Well - they're sometimes referred to as "BEFORE" triggers, since that's where they occur...but the official name is an INSTEAD OF trigger. INSTEAD OF triggers can be set up to make an action happen prior to actual insertion (i.e. a true BEFORE trigger), or to prevent/modify the insertion.
Matt,
My understanding of INSTEAD OF triggers is that they "intercept" the action and perform another action, so the original action could never actually be done. Having only seriously worked with SQL Server, I do not really know BEFORE triggers, but my understanding, correct me if I am wrong, is that they ALWAYS finish the original action (barring or course an error in the trigger that causes a rollback). This is the difference between SQL Server's INSTEAD OF triggers and true BEFORE triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2008 at 9:40 am
awesome I will give that a try, that looks close to what i'm looking for..thanks a bunch
May 14, 2008 at 9:49 am
Jack Corbett (5/14/2008)
Matt Miller (5/14/2008)
Jack Corbett (5/14/2008)
SQL Server does not have before triggers. In SQL Server you would use an after trigger and then use the virtual inserted and deleted tables. I will post an example later. You are using SQL Server, not DB2?Well - they're sometimes referred to as "BEFORE" triggers, since that's where they occur...but the official name is an INSTEAD OF trigger. INSTEAD OF triggers can be set up to make an action happen prior to actual insertion (i.e. a true BEFORE trigger), or to prevent/modify the insertion.
Matt,
My understanding of INSTEAD OF triggers is that they "intercept" the action and perform another action, so the original action could never actually be done. Having only seriously worked with SQL Server, I do not really know BEFORE triggers, but my understanding, correct me if I am wrong, is that they ALWAYS finish the original action (barring or course an error in the trigger that causes a rollback). This is the difference between SQL Server's INSTEAD OF triggers and true BEFORE triggers.
It does intercept the original action, but if you "reissue" the action from within the trigger - it will complete. Reissuing=creating an insert command based on the INSERTED table (and no - this doesn't retrigger the INSTEAD OF trigger).
But yes - the other distinctions are well noted. They ultimately are NOT the same as the BEFORE trigger from Oracle or DB2 (although the ability to "override" the value in a BEFORE UPDATE trigger kind of opens the door to "cancelling the update".) It does allow you to kind of emulate its behavior however, so it's why you might hear it referred to as the BEFORE trigger.
For example - if I wanted to "override" a specific field....
[font="Courier New"]CREATE TRIGGER ins_emp ON emp INSTEAD OF INSERT
AS
BEGIN
INSERT emp (empid1,empid2,name1,name2,joindate)
SELECT empid1,empid2,name1,name2,GETDATE()-1 FROM inserted
END
[/font]
And note that that really isn't 2 operations since the first insert technically hasn't happened.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 19, 2008 at 9:33 am
I believe INSTEAD OF means just that... if you do not do your Insert/Update/Delete in the Trigger, it does not get done. An Insert/Update/Delete will not be done automatically after the Instead of Trigger fires if there are no other triggers involved.
Toni
May 27, 2008 at 2:27 pm
Is there a way to do this to a specific record when a portion of that record changes? I keep getting errors when I try the above solutions cause I'm trying to edit multiple records. Really I only need to record the changes for a particular record. For example of I have a ticket 12344 and it has values for AgrPkey=V and some changes it to AgrPkey=B, I want to record in a different table what it was before and what it is after. Any help will be cool. Thanks
May 27, 2008 at 8:16 pm
Is there a special record combination you are looking for? Do you want 2 rows before and after or 1 row with before and after columns for specific columns?
You can do one of the triggers I noted and also store the deleted record and the inserted record using the inserted and deleted tables if you do a union query and insert the records from the inserted table in the second select.
If you post some schema, test data, and desired outcomes we can test the triggers as well. See the link in my signature for a tip on how to post test data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2008 at 7:04 am
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
Ticket INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
AgreeId INT,
LocId INT,
Customer nvarchar(50),
Type nvarchar(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(Ticket, AgreeId, LocId, Customer, Type)
SELECT '177987','2206','1103','Bobs BBQ','M' UNION ALL
SELECT '177988','3123','1345','Bobs Box of Toys','K' UNION ALL
SELECT '177980','2111','1654','Bobs Burgers','B' UNION ALL
SELECT '177990','1206','1289','Bobs Bakery','M' UNION ALL
SELECT '177991','3206','3241','Bobs Bar','M' UNION ALL
SELECT '177992','2223','3621','Bobs Shanty','M' UNION ALL
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
What I'm ultimately looking to do is if someone changes the AgreeId on ticket 177987 to 4126 I want to record in a separate table the following:
INSERT INTO TicketMasterHistory
(Ticket, AgreeId, LocId, Customer, Type, NewAgreeId, NewLocId)
Values
(177987,2206,1103,'Bobs BBQ', 'M', 4126, 1265)
Hope this helps, any help will be appreciated. Right now i'm doing this in ASP, but I would like to have a more global solution rather then having to retype the code in every page that has the potential to make a change.
Thanks
May 28, 2008 at 7:47 am
mbender (5/28/2008)
What I'm ultimately looking to do is if someone changes the AgreeId on ticket 177987 to 4126 I want to record in a separate table the following:
INSERT INTO TicketMasterHistory
(Ticket, AgreeId, LocId, Customer, Type, NewAgreeId, NewLocId)
Values
(177987,2206,1103,'Bobs BBQ', 'M', 4126, 1265)
Where did the 1265 (NewLocId) come from in this result? Here is what I put together using your test information:
[font="Courier New"]--===== If the test table already exists, drop it
IF OBJECT_ID('test.mytable','U') IS NOT NULL
DROP TABLE test.mytable
--===== Create the test table with
CREATE TABLE test.mytable
(
Ticket INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
AgreeId INT,
LocId INT,
Customer NVARCHAR(50),
TYPE NVARCHAR(50)
)
--===== If the test history table already exists, drop it
IF OBJECT_ID('test.mytable_history','U') IS NOT NULL
DROP TABLE test.mytable_history
--===== Create the test history table with
CREATE TABLE test.mytable_history
(
Ticket INT, --Is an IDENTITY column on real table
AgreeId INT,
LocId INT,
Customer NVARCHAR(50),
TYPE NVARCHAR(50),
NewAgreeId INT,
NewLocId INT
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT test.mytable ON
--===== Insert the test data into the test table
INSERT INTO test.mytable
(Ticket, AgreeId, LocId, Customer, TYPE)
SELECT '177987','2206','1103','Bobs BBQ','M' UNION ALL
SELECT '177988','3123','1345','Bobs Box of Toys','K' UNION ALL
SELECT '177980','2111','1654','Bobs Burgers','B' UNION ALL
SELECT '177990','1206','1289','Bobs Bakery','M' UNION ALL
SELECT '177991','3206','3241','Bobs Bar','M' UNION ALL
SELECT '177992','2223','3621','Bobs Shanty','M'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT test.mytable OFF
-- create the trigger
CREATE TRIGGER test.trg_MyTable_upd ON test.MyTable
FOR UPDATE
AS
IF UPDATE(AgreeId) -- remove this if you want to log changes other than to AgreeID
BEGIN
INSERT INTO test.mytable_history
SELECT
D.Ticket,
D.AgreeId,
D.LocId,
D.Customer,
D.TYPE,
I.AgreeID AS NewAgreeId,
I.LocID AS NewLocID
FROM
inserted I JOIN -- new record
deleted D ON -- existing record
I.Ticket = D.Ticket AND
I.AgreeID <> D.AgreeID -- this ensures that AgreeID was changed
END
-- now update
BEGIN TRANSACTION
UPDATE test.MyTable
SET AgreeId = 4126,
LocId = 1265
WHERE
Ticket = 177987
COMMIT TRANSACTION
-- return history record
SELECT * FROM test.MyTable_History
-- history result
Ticket AgreeId LocID Customer TYPE NewAgreeId NewLocId
177987 2206 1103 Bobs BBQ M 4126 1265
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2008 at 8:12 am
I think this will do what you are after and will handle multiple updates.
Toni
--===== If the test table already exists, drop it
IF OBJECT_ID('mytable','U') IS NOT NULL
DROP TABLE mytable
IF OBJECT_ID('TicketMasterHistory','U') IS NOT NULL
DROP TABLE TicketMasterHistory
--===== Create the test table with
CREATE TABLE mytable
(
Ticket INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
AgreeId INT,
LocId INT,
Customer nvarchar(50),
Type nvarchar(50))
--===== Create the test table with
CREATE TABLE TicketMasterHistory
(
Ticket INT PRIMARY KEY CLUSTERED,
AgreeId INT,
LocId INT,
Customer nvarchar(50),
Type nvarchar(50),
NewAgreeId INT,
NewLocId INT)
go
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT mytable ON
--===== Insert the test data into the test table
INSERT INTO mytable
(Ticket, AgreeId, LocId, Customer, Type)
SELECT '177987','2206','1103','Bobs BBQ','M' UNION ALL
SELECT '177988','3123','1345','Bobs Box of Toys','K' UNION ALL
SELECT '177980','2111','1654','Bobs Burgers','B' UNION ALL
SELECT '177990','1206','1289','Bobs Bakery','M' UNION ALL
SELECT '177991','3206','3241','Bobs Bar','M' UNION ALL
SELECT '177992','2223','3621','Bobs Shanty','M'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT mytable OFF
IF OBJECT_ID('updatetk','TR') IS NOT NULL
DROP Trigger updatetk
go
create trigger updatetk on mytable after update
-- Update trigger to record changes made to AgreeId
AS
IF UPDATE(AgreeId)
BEGIN
INSERT INTO TicketMasterHistory
(Ticket, AgreeId, LocId, Customer, Type, NewAgreeId, NewLocId)
SELECT d.Ticket, d.AgreeId, d.LocId, d.Customer, d.Type, i.AgreeId, i.LocId
FROM deleted d
JOIN inserted i on d.Ticket = i.Ticket
END
go
UPDATE mytable SET AgreeId=4126
where Ticket=177987
select * from mytable
select * from TicketMasterHistory
May 28, 2008 at 8:16 am
What Jack said. Looks like I am much slower than him.
Toni
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply