March 17, 2009 at 3:50 pm
If any new inserts, then I need to insert the data into table1 and if any existing data is getting updated, then update the existing data in table1.
And also I need to write a trigger on table1, where only the updated information needs to be stored in the table1_upd table. Please let me know the best way of doing this. Thanks.
March 17, 2009 at 4:50 pm
[font="Verdana"]The best way is to use gateway stored procedures to handle your inserts and updates. So they can insert and update into the various tables as necessary.
Failing that, use triggers.[/font]
March 17, 2009 at 5:11 pm
Bruce W Cassidy (3/17/2009)
[font="Verdana"]The best way is to use gateway stored procedures to handle your inserts and updates. So they can insert and update into the various tables as necessary.Failing that, use triggers.[/font]
I whole heartedly agree with Bruce, but one thing that is worth emphasizing is that you should only rely on the gateway procedure if every single update/insert that you need to be affected can be gaurunteed to go through the gateway procedure.
If you can gauruntee that, then it avoids the "hidden code" that triggers bring and can be more effecient. But if you cannot gauruntee all of them will use the gateway procedure then the triggers will be more reliable.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 17, 2009 at 5:21 pm
timothyawiseman (3/17/2009)
Bruce W Cassidy (3/17/2009)
[font="Verdana"]The best way is to use gateway stored procedures to handle your inserts and updates. So they can insert and update into the various tables as necessary.Failing that, use triggers.[/font]
I whole heartedly agree with Bruce, but one thing that is worth emphasizing is that you should only rely on the gateway procedure if every single update/insert that you need to be affected can be gaurunteed to go through the gateway procedure.
If you can gauruntee that, then it avoids the "hidden code" that triggers bring and can be more effecient. But if you cannot gauruntee all of them will use the gateway procedure then the triggers will be more reliable.
Affirmative... I'm also really no friend of triggers but an history table is one of the rare but good reasons for it. If you can guarantee that no other module will write into the table use a gateway procedure (cool perception - never heard ;-)). If not guaranteed you should use a trigger...
Greets
Flo
March 17, 2009 at 5:45 pm
[font="Verdana"]You can generally use object-level security to ensure that the only the stored procedure has rights to write to the table.[/font]
March 17, 2009 at 5:52 pm
Bruce W Cassidy (3/17/2009)
[font="Verdana"]You can generally use object-level security to ensure that the only the stored procedure has rights to write to the table.[/font]
Hello Bruce
Absolutely correct! The question just is if there already exists a set of modules (processes, guis, web-apps) which potentially already using the table.
Greets
Flo
March 17, 2009 at 6:45 pm
[font="Verdana"]Hey Flo 😀
Yup. Sometimes the existing processes make it impossible to move to something like gateway procedures, hence why I said "failing that, use triggers." Some sort of judgement call is required there.
But if you can change the existing processes to use the gateways, then you can lock down the tables so only the gateways write to them to prevent ad-hoc changes. That way you don't need a trigger to "guarantee" that the necessary history/auditing, etc, is maintained.
I also looked at designing gateways that receive a chunk of XML so they can do set-based inserts, updates, deletes, etc. It's not too hard to generate XML from a .Net (or Java) application to do that. That works quite well with something like (as an example) a purchase order, where you can send the entire purchase order to the database as an XML string, and have the stored procedure write out the necessary purchase order header, purchase order lines, summaries and so on as a set and as one transaction.
Even taking into account the additional time to do the XML handling, the performance of the persistance layer goes up dramatically with that sort of approach.
So with the right approach, using gateway procedures gives a lot of benefit, and need not require row-by-row persistence.
I've almost come to regard triggers as the database equivalent of the goto. That or cursors. Hmm.
[/font]
March 18, 2009 at 2:54 am
Hi Bruce
I complete confirm you! 🙂
I just thought about my own project. It's an 9 year old monster with over 80 processes and apps in c/c++/c#/java/vb/workflows and it's almost impossible to know which modules access which data...
Greets
Flo
March 18, 2009 at 4:08 pm
I also need a trigger on the final table, where only the updated information needs to be
stored in the table1_upd table. Could you please send me trigger code for me to refer to for this purpose.
So far I have the follow code:
--import into a staging table
-- Add new employees
INSERT FinalTable
SELECT
Employee_ID as EMPLID,
fieldn
FROM dbo.StagingTable
WHERE NOT EXISTS
(SELECT Employee_ID FROM FinalTable
WHERE Employee_ID = StagingTable.EMPLID)
-- Update existing employees
UPDATE FinalTable
SET fieldn = StagingTable.fieldn
FROM dbo.StagingTable
WHERE
FinalTable.Employee_ID = StagingTable.EMPLID
Thanks!
March 18, 2009 at 5:00 pm
[font="Verdana"]I'm sorry, but aren't you employed to write the code? Why are you asking us to write the code for you? If you write it and get stuck, then sure, I'd be happy to offer advice. But I'm not going to do your job for you.[/font]
March 19, 2009 at 6:24 am
Bruce W Cassidy (3/17/2009)
[font="Verdana"]You can generally use object-level security to ensure that the only the stored procedure has rights to write to the table.[/font]
Technically true, but I have been in shops where actually doing that is not practical due to legacy code, office politics that dictate certain people will have full SA rights, etc.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
March 19, 2009 at 6:34 am
timothyawiseman (3/19/2009)
Bruce W Cassidy (3/17/2009)
[font="Verdana"]You can generally use object-level security to ensure that the only the stored procedure has rights to write to the table.[/font]Technically true, but I have been in shops where actually doing that is not practical due to legacy code, office politics that dictate certain people will have full SA rights, etc.
I also saw this... since the first real maintenance is needed with restricted access and one of the "certain people" was still on database 😀
Greets
Flo
March 19, 2009 at 12:51 pm
I wrote the follow update trigger, please let me know if this okay or do I need to make any changes. Thanks.
Create trigger dbo.updatetrigger
on dbo.Employee
After Update
AS
Begin
set nocount on;
Insert into dbo.EmployeeModified
(emp_id,dept_id,firstname,lastname,address_line_1,city,datetimelog)
Select i.emp_id
,i.dept_id
,case when i.firstname <> d.firstname then i.firstname else d.firstname End
,case when i.lastname <> d.lastname then i.lastname else d.lastname End
,case when i.address_line_1 <> d.address_line_1 then i.address_line_1 else d.address_line_1 End
,case when i.city <> d.city then i.city else d.city End
,getdate()
from Inserted i
left join Deleted d
on i.emp_id = d.emp_id
March 19, 2009 at 2:37 pm
I changed the trigger code, please review and let me know if it's okay. Thanks.
Create trigger dbo.updatetrigger
on dbo.Employee
After Update
AS
Begin
set nocount on;
Insert into dbo.EmployeeModified
(emp_id,dept_id,firstname,lastname,address_line_1,city,datetimelog)
Select i.emp_id
,i.dept_id
,i.firstname
,i.lastname
,i.address_line_1
,i.city
,getdate()
from Inserted i
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply