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  General SQL Server Forums  New to SQL Server Programming  Creating Triggers

Author  Topic  MahiSan Starting Member 1 Post Posted - 2013-10-11 : 15:16:44 I Guys, I need big help to create these triggers. I tried many different things but couldn't get it working using if exist, case statements..etc. Please help me to complete this task :) For Insert into the EMP table: EMP TABLE or EMP_AUDIT([ENO] [numeric](4, 0) NOT NULL,[ENAME] [varchar](10) NOT NULL,[SAL] [numeric](7, 2) NOT NULL,[COMM] [numeric](7, 2) NULL,[DNO] [numeric](2, 0) NULL,[DNAME] [varchar](20) NULL EP Table or DNO_Audit [DEPTNO] [numeric](3, 0) NOT NULL,[DNAME] [varchar](20) NOT NULL,[LOC] [varchar](13) NULL,[Tot_EMPS] [numeric](3, 0) NULL,[Tot_SALs] [numeric](10, 2) NULL A new employee is entered by HR personnel by providing the following: • ENO, ENAME, SAL, DNAME • Note: HR people do not know the DNO, you need to use your store procedure to figure that out. You can not change the nullability of table EMP or DEPT Write a trigger that when a new employee is added: • Checks the uniqueness of ENO If ENO is NOT unique then it inserts the information about the employee into an audit table(2nd table,The audit table have the same column structure as the EMP table) • Checks the DNAME to see if it is in Department table. – If it exists, gets the value of DNO to be used in EMP table. Otherwise, it adds the employee to the audit table • It also checks the department name to see if it is SALES. If it is, then provides value of 300 for commission, otherwise it sets the commission to zero. • Then inserts employee into the EMP table, and finally • Updates Tot_Sals and Tot_EMPS of the department table For Update of the EMP table: For Delete from the EMP table:When an employee is deleted from the system, you need to update the tot_Sals and tot_emps in the DEPT table When the salary of an employee is updated, a trigger compares the new salary and the old salary. The trigger only performs the update if the new salary is more than the old salary. In that case, the trigger updates the Tot_Sals in DEPT table – otherwise, no action is taken, the information is audited, and the update is undone. (You will use function you have written to check for this. ) The trigger also checks if DNAME has been updated. • If DNAME is changed – If the new department name is not in the department table, then it audits the employee that is being updated. If the new department is ‘Sales’ then proper commission ($300) is entered into the commission field for the employee – It will update tot_sals and tot_emps for both the old department and the new department for the employee