Hello,
I have a need to do something in a trigger, I have read all the in's and out's of when to use or not to use a trigger, so I am not looking for an answer like that.
What i am wondering is how to get the fields from a record that triggered the trigger.
for example
an on insert trigger......
Inserted record example:
Field names: key field1 field2 field 2
field data 1 Jack filed baseball
i want to do a trigger that will insert the differnt fields into differnt tables
on insert (very rough example)
insert field1 into names
insert field2 into status
insert field3 into sports
++++++++++++++
or better yet is it possiable to send var's to a stored procedure for example:
create procedure [insert data]
(@ID [int],
@field1 [nvarchar](10),
@field2 [nvarchar](10),
@field3 [nvarchar](10))
as
insert @field1 into names
insert @field2 into status
insert @field3 into sports
++++++++++++++
ON record insert have a trigger send the data for the record that was inserted to the stored procedure.
Hope this makes sence:
Tdar
triggers and record data question
ChrisMentioned
Hi,
thanks for the answers, I guess I have to go into more details.
I have had this program that pulls data from a DBF file using Access, I have got this process down to submiting one SP for each record to update the online inventory table.
another words
Do untill datacommand.eof
'execute the sp based on values of each record
'i leave the connection open to the online server during this process
Now there is the problem because we have to do this thru the internet connection it takes 4-6 hours depending on the record count average 60,000 records per update.
What I was hoping to send the DBF up to the SQL server 2k using the DTS inport program and on insert of the record have that run the SP..
( the SP is pritty big since i have to deal with alot of factors, i can post if if you really need to see it)
this way it would eliminate the slowness caused by the internet connection and access( currently using ADO .. I have not converted the code to ADO.NET 2 yet)
Thus instead of tying up this sytem for 4-6 hours the process could be done on the server, and the server has alot of unused processing power....
So i guess my question is now how would/should I go about this, to make this processing much faster.
One last note, My Dream would be when his system makes a change to the inventory it just lets me up date that real time when the change was made, but this is a very old dbman database software and The owner of the company is the programer and well he does not have much time.
David
love_1776
So could I run a SP from a trigger
like:
begin
Begin Transaction
DECLARE @RC int
DECLARE @key [int]
DECLARE @field1 [nvarchar](10)
DECLARE @field2 [nvarchar](10)
select key as @key, field1 as @field1, field2 as @field2 from inserted
EXEC @RC = [OFFLINE_TEST] @key, @field1, @field2
End Transaction
-- additional note
-- i dont know how to tell if a transaction failed and i would like it to tell me
-- if it fails by updateing the inserted records status field
-- is this also possiable...
-- or maybe the value of @RC would tell me if that sp failed and then use it here
-- or maybe i could update that table using the key
update inserted set status = 1
--or ... update inserted set status = 1
end
cerious about this answer, then I will explain what I am doing and another thought I just had of a different way to do this not using triggers..
Tdar
Jeremy Lawrence
SiteGeek
maybe you can also try this.
have several tables that looks like the destination table as uma says (staging tables).
if you have 60,000 records you can divide it into 6 sets. import each set of data to tables namely staging1, staging2, staging3 and so on. in this way you have six process running at the same time.
when your done with the import. Load it to the destination table using bulk insert
if your using sqlserver2005 you can make use of table partition and upon import completion swicth the stage partion to that of the destination table
after the entire merging is done. thats the time to run your SPs to maintain database consistency.
Spuddo
Is there anything you two could sugest to read up on or a web cast that shows this in SQl 2k, I assume this can be done in sql 2k that is.
tdar
TheJet
In the simplest case, you could do below in the trigger code:
begin
insert into names
select key, field1 from inserted
insert into status
select key, field2 from inserted
insert into sports
select key, field3 from inserted
end
In SQL Server 2005, you can also take a look at the OUTPUT clause which you can use in the INSERT statement to capture resutts into the audit table easily. You can look at the blog entry below for some examples of the OUTPUT clause:
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx
One thing that is not clear from your request is the reason for the denormalization of the data. This will use more storage and cause complexity in terms of joining the results using the audit tables for example.
Andy089
I agree with Uma here. Its difficult to pass the result of inserted and deleted tables to the SP.
In some circumstances however what i do is
to have a trigger that calls an SP that then
process the data to the desired state from the base tables
without having to care what is inside the inserted/deleted table.
the other way to do it if your in a desperate need of the inserted/deleted tables is
push the data from inserted/deleted tables into #temp tables. later on call
the SP that process the data from #temp table.
David Eccleston