alternative of triggers - 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 2005 Forums

Transact-SQL (2005)

alternative of triggers

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2009-12-10 : 14:47:39

UPDATE EMPLOYEE
SET EMP_STATUS_ID = ( SELECT STATUS_ID FROM STATUS_LOOKUP WHERE STATUS_CODE=100)
FROM EMPLOYEE as x JOIN
( SELECT EMP_ID, CLASS_CODE
FROM EMPLOYEE as a
JOIN GRADE as b ON a.emp_id=b.emp_id
JOIN SALARY as c ON b.grade_id=c.greade_id
WHERE c.salary_greade_code in ( 10, 20)
) AS Y
ON x.emp_id=y.emp_id

BEFORE this update , want to have the old entry into EMP_AUDIT
( EMP_ID from employee , grade_id from grade , update_date)

Can not use the triger here as i need to have join on DELETED.and its taking a lot of time. Thats the reason i have to manage the insert before the update through SQL. please help

Byomjan....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-10 : 15:13:17

Why is joining to the DELETED trigger table taking a long time? Just how many rows is that thing updating?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

byomjan
Starting Member

34 Posts

Posted - 2009-12-14 : 12:28:06

some 90000+ rows.

Do you suggest making this join as a triger ?

Byomjan....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-14 : 21:16:43

[code]
-- Your UPDATE statement
UPDATE e
SET EMP_STATUS_ID = (
SELECT STATUS_ID
FROM STATUS_LOOKUP
WHERE STATUS_CODE=100
)
FROM EMPLOYEE e
WHERE EXISTS
(
SELECT *
FROM GRADE g
INNER JOIN SALARY s on g.grade_id = s.grade_id
WHERE g.emp_id = e.emp_id
AND s.salary_greade_code IN ( 10, 20 )
)

-- Update Trigger Code
INSERT INTO EMP_AUDIT (emp_id, grade_id, update_date)
SELECT d.EMP_ID, g.grade_id, getdate()
FROM deleted d
INNER JOIN GRADE g ON d.emp_id = g.emp_id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources