Need Trigger Help Please - SQL Server Forums

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

Transact-SQL (2000)

Need Trigger Help Please

Author

Topic

Capt_Ron
Starting Member

45 Posts

Posted - 2006-01-19 : 10:50:26

Here's what I have to do:
I have a 2 tables tblApproval and tblRequest
When a record is Inserted into tblApproval
I need to update a record in tblRequest

Here are some specifics.
I have 2 fields in tblApproval
Approval (can only be "Approved" or Denied") and Department (can only be "Accounting" or "Personnel")
I have 2 field in tblRequest
AccountingApproval (bit 1 or 0) and PersonnelApproval (bit 1 or 0)


IF tblApproval.Department = "Accounting" THEN
 IF tblApproval.Approval="Approved" THEN
  SET tblRequest.AccountingApproval = 1
 ELSE
  SET tblRequest.AccountingApproval = 0
 END IF
ELSE
 IF tblApproval.Approval="Approved" THEN
  SET tblRequest.PersonnelApproval = 1
 ELSE
  SET tblRequest.PersonnelApproval = 0
 END IF
END IF

How can I make this into a Trigger so I don't have to code it in the application?

Thank you very much for your help
Ron

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-19 : 23:09:38

use a update statement with case

CREATE TRIGGER [TriggerName] ON [dbo].[TblApproval]
FOR INSERT, UPDATE

AS
BEGIN
update tblRequest set PersonnelApproval = case inserted.department when 'Accounting' then
case inserted.approval when 'Approved' then 1
else 0
end
else
case inserted.approval when 'Approved' then 1
else 0
end
end
from inserted where
tblapproval.<KeyColumn> = inserted.<Keycolumn>
END

Capt_Ron
Starting Member

45 Posts

Posted - 2006-01-23 : 07:59:47

Thank you.
I appreciate your help.
I'll give it a try.
Ron

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources