Triggers and inserted/deleted tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Triggers and inserted/deleted tables

Author

Topic

Sully
Starting Member

30 Posts

Posted - 2006-04-26 : 16:55:01

Hi All,

I don't have much experience with triggers, so I have researched this site, BOL, and other resources. My question pertains to the life of the inserted/deleted temp tables. I'm putting in an audit trigger on one of our tables:

CREATE TRIGGER pp_LoadContraAudit ON dbo.BZCONTRACT
FOR UPDATE, DELETE
AS

insert into bzcontraudit
select d.con_contract_number, d.con_audit_date_time , d.con_cont_numb_type, d.con_qual_last_name, d.con_qual_first_name, d.con_qual_mi,
d.con_qual_suffix, d.con_mailing_stnum, d.con_mailing_stname, d.con_mailing_sttype, d.con_mailing_stdir, d.con_mailing_apt_number,
d.con_mailing_cz, d.con_phone, d.con_wk_comp_exp_date, d.con_liability_exp_date, d.con_category, d.con_license_exp_date,
d.con_wk_comp_can_date, d.con_liability_can_date, d.con_license_suspended, d.con_license_susp_date, d.con_license_sus_numb_days,
d.con_contractor_status, d.con_inactive_message, d.con_firm_name,
d.con_vs_flag, d.con_audit_user_id,
d.con_audit_program, case when I.con_contract_number is null then 'D' else 'U' end
from deleted d left outer join inserted I on d.con_contract_number = I.con_contract_number

I read here the info about multiple rows possibly existing in the tables. I want to make sure I understand this correctly. My take on this is that the only way multiple rows would exist in the table is when a trigger is fired and the io command in question manipulates multiple rows, like below.

update bzcontract
set con_vs_flag = 'Y'
where con_contract_number like '17%'

So the only rows that would be found in the two temp tables, are from this specific update. If the same user issues a delete immediately after the update, the trigger would fire a second time and the rows in the deleted table would only contain the rows affected by the delete.

delete bzcontract
where con_contract_number = '1712231223'

In this case only one row would be in deleted and nothing in inserted. It would only make sense for it to be this way, or the data in these tables couldn't be trusted. However, stranger things do happen...lol. I have tried testing this through SQL Analyzer, getting the results I would expect. But some of our SQL access is through a third party interface, using dynamic SQL. So, is my thinking correct?

Any help will be greatly appreciated. I hope I've explained it better than I understand it.

Stuck in neutral in a pedal to the metal world,
Sully

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-26 : 17:23:17

Your thinking is correct. Since you can only issue one sql statement at a time (insert OR update OR delete) A trigger (after Update, Delete) would be dealing with only an one statement at a time. And yes, if the statement affected multiple rows then those rows affected would be in the inserted and/or deleted tables.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-04-27 : 01:53:03

"I hope I've explained it better than I understand it"

Tehre must be a Latin moto for this ... something like:

Explainicus wellicus, understandicus perfecticus!

Kristen

Sully
Starting Member

30 Posts

Posted - 2006-04-27 : 09:30:48

Thanks TG, I appreciate the knowledge...and Kristen lol!

Stuck in neutral in a pedal to the metal world,
Sully

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources