August 29, 2005 at 7:52 am
Please help
I'm trying to create a trigger that grabs the row being inserted updated or deleted in a given table and inserts it to another almost identicle table. For some reason I'm having a terrible tome making it work. Could someone please give me a simple example to build off of.
Here is an example of the source table where the trigger will reside using some simple column names.
tblMembers
ID Guid
Fname Varchar
Lname Varchar
Address Varchar
The desination table would look something like This
TX_tblMembers
ID Guid
Fname Varchar
Lname Varchar
Address Varchar
tx_ID A new Guid
tx_type Value will be a i, u, or d depending on if the
transaction was inserted updated or deleted.
tx_time A new date time
TX_Status Will always default to 0
Here is what want the rigger to look like but I kep getting errors.
on insert, update, delete,
on insert
Select ID, fname, ID, address from inserteted into TX_tblmembers
Set tx_tblmemebers.tx_type = i (for last transaction. I'm not sure how to do this)
on update
Select ID, fname, ID, address into TX_tblmembers from updated
Set tx_tblmemebers.tx_type = u
on Delete
Select ID, fname, ID, address into TX_tblmembers from deleted.
Set tx_tblmemebers.tx_type = d
Please help. keep in mind that I'm very new to SQL programming so the simpler the better.
August 29, 2005 at 8:25 am
CREATE TRIGGER INS_tblmembers
ON tblmembers
FOR INSERT
AS
INSERT INTO TX_tblmembers
Select ID, fname, lname, address,newid(),'i',getdate(),0 from inserted
CREATE TRIGGER DEL_tblmembers
ON tblmembers
FOR DELETE
AS
INSERT INTO TX_tblmembers
Select ID, fname, lname, address,newid(),'d',getdate(),0 from deleted
CREATE TRIGGER UPD_tblmembers
ON tblmembers
FOR UPDATE
AS
INSERT INTO TX_tblmembers
Select ID, fname, lname, address,newid(),'d',getdate(),0 from deleted
INSERT INTO TX_tblmembers
Select ID, fname, lname, address,newid(),'i',getdate(),0 from inserted
Vasc
August 29, 2005 at 8:28 am
Hey vasc, what's the reasonning behind the update trigger?
Why do you do insert deleted, insert inserted instead of doing a single insert from inserted and marking it as 'U'?
August 29, 2005 at 8:32 am
None beside copy-paste : )
Vasc
August 29, 2005 at 8:34 am
I thaught that it may have something to do with reporting but looks like I was wrong...
August 29, 2005 at 8:37 am
Hey thats great but can I build this into a single trigger? That's where I'm really having a big problem.
Thank you
August 29, 2005 at 8:45 am
CREATE TRIGGER IDU_tblmembers
ON tblmembers
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO TX_tblmembers
Select ID, fname, lname, address,newid(),'d',getdate(),0 from deleted
INSERT INTO TX_tblmembers
Select ID, fname, lname, address,newid(),'i',getdate(),0 from inserted
Vasc
August 29, 2005 at 9:43 am
cool cool, but how do I determine if the transaction was inserted, updated or deleted?How do I capture that value to a tx_table?
Thank you so much..,.
August 29, 2005 at 10:01 am
I suggest you familiarize yourself with what BOL has to say about CREATE TRIGGER, SELECT INTO, and other related stuff, and also follow any available links to familiarize yourself both with syntax and concepts.
Some of the problems I see, off the bat, are that you are trying to use SELECT INTO in lieu of an INSERT. If your 'TX_tblMembers' table is a permanent table, then every time you run a trigger with a 'SELECT * INTO TX_tblMembers' statement, T-SQL is going to complain that the table already exists.
You use the datatype term 'GUID' -- in T-SQL, that is called 'UNIQUEIDENTIFIER'
Any given triggering statement may have affected zero rows. Triggers are already expensive enough, therefore there is no need to make them more expensive by hanging around unnecessarily.
If you use one trigger for all three operations -- DELETE, INSERT, UPDATE -- you will need to test for whichever operation has triggered the current firing. (If you write three separate triggers, however -- one for INSERT, one for UPDATE, and one for DELETE -- you will not need any such test.)
If you will be inserting fewer columns into the 'TX_tblMembers' table than its definition includes, you will also need to include a list of columns along with the INSERT. Alternatively, any defaulted values can be set here at the INSERT, or as DEFAULT values in the table declaration. A new DATETIME value (for column 'txTime') can be inserted as GETDATE ().
I can provide a sample template, so long as you understand I make no warranties or guarantees of any sort.
CREATE TABLE tblMembers
(ID UNIQUEIDENTIFIER
, Fname Varchar (35)
, Lname Varchar (35)
, Address Varchar (60))
CREATE TABLE TX_tblMembers
(ID UNIQUEIDENTIFIER
, Fname Varchar (35)
, Lname Varchar (35)
, Address Varchar (60)
, tx_ID UNIQUEIDENTIFIER
, tx_type Char (1) -- i, u, or d
, tx_time DATETIME
, TX_Status INT DEFAULT (0))
CREATE TRIGGER T_DIU_tblMembers
ON tblMembers
AFTER DELETE
, INSERT
, UPDATE
AS
/*
||==============================================
|| No rows affected? Then scram!
||==============================================
*/
DECLARE @rowcount INT
SELECT @rowcount = @@ROWCOUNT
IF @rowcount = 0
--THEN
GOTO TRG_EXIT
--END IF
/*
||==============================================
|| Is this an INSERT, UPDATE, or DELETE?
||==============================================
*/
DECLARE @operation CHAR (1)
, @nr_inserted INT
, @nr_deleted INT
SELECT @nr_inserted = COUNT (1)
FROM inserted
SELECT @nr_deleted = COUNT (1)
FROM deleted
IF @nr_inserted > 0
AND @nr_deleted > 0
--THEN
SELECT @operation = 'U'
ELSE IF @nr_inserted > 0
--THEN
SELECT @operation = 'I'
ELSE
SELECT @operation = 'D'
--END IF
/*
||==============================================
|| Stash the results...
||==============================================
*/
INSERT INTO TX_tblMembers (ID
, Fname
, Lname
, Address
, TX_ID
, TX_TYPE
, TX_TIME)
SELECT results.ID
, results.Fname
, results.Lname
, results.Address
, NEWID ()
, @operation
, GETDATE ()
FROM (SELECT *
FROM inserted
WHERE @operation IN ('I', 'U')
UNION ALL
SELECT *
FROM deleted
WHERE @operation = 'D') results
TRG_EXIT:
GO
August 29, 2005 at 3:36 pm
Man. you rock. that seems to be working pretty well. Unfotunalty I have 500 + tables to create this on. Is there a way to speed that up to create new TX tables and triggers?
August 29, 2005 at 3:57 pm
> Man. you rock. that seems to be working pretty well.
Glad I could help.
> Is there a way to speed that up to create new TX tables and triggers?
That's hard to say. How generic do you want your triggers? There's going to be a trade-off between being generic and having good performance.
You could write a script that would produce a character string for each table containing trigger code, and then execute it. Roughly, you would loop on all the table names and (querying 'syscolumns') substitute the column names within that string whenever appropriate. That might take as long as doing it manually.
August 30, 2005 at 3:55 am
I have used what I think is a somewhat simpler method for my triggers. The trigger will only fire if there are rows affected so checking for rows affected is unnecessary.
Here is a small example of the trigger body:
Insert Into Table
/* UPDATED RECORDS */
Select
I.fields,
'U' as type
From
inserted I Join
deleted D On
I.PK = D.PK
Union All
/* INSERTED RECORDS */
Select
I.fields,
'I' as type
From
inserted I
Union All
/* DELETED RECORDS */
Select
D.fields,
'D' as type
From
deleted D
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
August 30, 2005 at 6:39 am
> The trigger will only fire if there are rows affected so checking for rows affected is unnecessary.
How do you do that?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply