problem with the trigger doesnt insert

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

problem with the trigger doesnt insert

Author

Topic

SD_Monkey
Starting Member

38 Posts

Posted - 2010-08-04 : 14:15:32

[code]
CREATE TRIGGER t_ins_payslip ON [dbo].[payslip]
FOR INSERT

AS
DECLARE @payid AS INT
DECLARE @payfrom AS DATETIME
DECLARE @payto AS DATETIME

SELECT @payid=i.payid, @payfrom=i.payfrom,@payto=i.payto FROM INSERTED i

INSERT INTO timebook(payid,empdetid,wday,rpd,trw,rothrs,rotw,hothrs,hotw,gi,sss_ee,sss_er,hdmf_ee,hdmf_er,ph_ee,ph_er,cola,ni)
SELECT @payid,empdetid,wdy,rpd,(totalhrs * rpd) as trw,[Regular],([Regular] * rpd) ramount,[Holliday],([Holliday] * rpd) as hamount,
tamount,[sss ee],[sss er],[hdmf ee],[hdmf er],[ph ee],[ph er],cola,netincome FROM
(SELECT *,((tamount - ([sss ee] + [ph ee] + [hdmf ee])) + cola) as netincome FROM
(SELECT *,dbo.fn_sss_ee(salary) as [sss ee],dbo.fn_sss_er(salary) as [sss er],dbo.fn_ph_ee(salary) as [ph ee],
dbo.fn_ph_er(salary) as [ph er],dbo.fn_hdmf_ee(salary) as [hdmf ee],dbo.fn_hdmf_er(salary) as [hdmf er],
dbo.fn_cola(empdetid,@payfrom,@payto) as cola FROM
(SELECT *,(totalhrs * rpd) + ([Regular] * rpd) + ([Holliday] * rpd) AS tamount FROM
(SELECT [EMPLOYEE ID],[NAME],wdy,totalhrs,salary,excemption,empdetid,CASE WHEN rothrs IS NULL THEN 0 ELSE rothrs END [Regular] ,
CASE WHEN hothrs IS NULL THEN 0 ELSE hothrs END AS Holliday,dbo.fn_per_day(salary,empdetid) AS rpd FROM
(SELECT *,dbo.fn_overtime_regular(@payfrom,@payto,empdetid) as rothrs,dbo.fn_overtime_holliday(@payfrom,@payto,empdetid) as hothrs FROM
(SELECT [EMPLOYEE ID],[NAME],COUNT(WDAY) AS wdy,SUM(TOTALHR) AS TOTALHRS,SALARY,EXCEMPTION,empdetid FROM
(SELECT D.[Employee ID],D.[Name],D.wday,D.totalhr,E.salary,E.excemption,E.empdetid FROM
(SELECT [EMPLOYEE ID],[NAME],COUNT([DATE]) AS WDAY,(AM_HR + PM_HR) AS TOTALHR FROM
(SELECT [EMPLOYEE ID],[NAME],[DATE],
CASE WHEN AM_HRS IS NULL THEN 0 ELSE AM_HRS END AS AM_HR,
CASE WHEN PM_HRS IS NULL THEN 0 ELSE PM_HRS END AS PM_HR FROM
(SELECT [Employee ID],[Name],(DATEDIFF(n,AM_TIMEIN,AM_TIMEOUT)/60) AS AM_HRS,
(DATEDIFF(n,PM_TIMEIN,PM_TIMEOUT) /60) AS PM_HRS,[Date]
FROM (SELECT [Employee ID],[Name],dbo.fn_validate_am_timein([Time In AM]) AM_TIMEIN,
dbo.fn_validate_am_timeout([Time Out AM]) AS AM_TIMEOUT,
dbo.fn_validate_pm_timein([Time In PM]) AS PM_TIMEIN,
dbo.fn_validate_pm_timeout([Time Out PM]) AS PM_TIMEOUT,[Date] FROM [DTR Info] WHERE [Date] BETWEEN @payfrom AND @payto)A)B)C
GROUP BY [EMPLOYEE ID],[NAME],AM_HR,PM_HR )D INNER JOIN [Employee Information] E
ON D.[EMPLOYEE ID]=E.[Employee ID])G GROUP BY [EMPLOYEE ID],[NAME],SALARY,EXCEMPTION,empdetid)F)H)I)j)l)m

if @@ERROR != 0
BEGIN

ROLLBACK TRANSACTION
END
[/code]

A maze make you much more better

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 14:20:12

http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

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

Subscribe to my blog

SD_Monkey
Starting Member

38 Posts

Posted - 2010-08-04 : 14:28:49

sorry i figured it out...

i use begin transaction on stored procedure but no commit transaction....

thank for the reply

A maze make you much more better

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 15:31:23

You trigger only works if a single record is inserted. Triggers need to work if there are multiple records in the batch.

SD_Monkey
Starting Member

38 Posts

Posted - 2010-08-04 : 19:45:16

but the trigger work on a multiple record,

can you explain further that your point of view..

A maze make you much more better

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-04 : 19:51:07

See the link I posted for why it won't work for multiple records.

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

Subscribe to my blog

SD_Monkey
Starting Member

38 Posts

Posted - 2010-08-04 : 20:02:52

thanks for the link....

i have another problem with the trigger...


CREATE TRIGGER t_del_emp ON [dbo].[empmas] 
FOR DELETE 
AS

	declare @empid varchar(10)
	SELECT @empid=d.empid FROM DELETED d
	declare @empdetid INT
	IF EXISTS(SELECT * FROM empdet WHERE empid=@empid AND deactivated is null)
	BEGIN
		SET @empdetid = (SELECT empdetid FROM empdet WHERE empid=@empid AND deactivated is null)
		UPDATE empdet SET deactivated=getdate() WHERE empdetid=@empdetid
	END

BEGIN TRANSACTION 
	

	if EXISTS (SELECT * FROM empdet WHERE empid=@empid)
	BEGIN
		RAISERROR 14001 'You cannot delete this Employee '
		ROLLBACK TRANSACTION
	END
	
	
COMMIT TRANSACTION

i want if record has exist on another table which deactivated is null then it only updated and not be deleted or record is existing, if record doesn't exist on the another table it will be deleted

A maze make you much more better

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-05 : 03:18:28

Its ok if there is not multiple delete.
One more thing is that Why you did not put the Begin and commit transaction block
where the actual transaction is being done ?

What you need help in that ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

SD_Monkey
Starting Member

38 Posts

Posted - 2010-08-05 : 04:21:42

i need only one delete and update...

it only verify if the record is existing then the record should validate and rollback, unless the record on the other table is not existing then on the table of empmas

here is the scenario

empmas where hold my personal information
and empdet hold my company information

if i have an company information then the record should not be deleted on my personal information to keep my historical
record for the organization
and if i update my company information my previous should expire and not be deleted and insert my new company record.


empmas
-------
empid
lname
fname
mname

empdet
--------
empdetid
empid
datehired
dateexpired
salary
position

let assume that my table design

my trigger wont work...

A maze make you much more better

Kristen
Test

22859 Posts

Posted - 2010-08-05 : 04:41:55

"i need only one delete and update..."

Someone, sometime, will process multiple records. You need to allow for that. Otherwise you will just process one row, at random.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148277#581997 where similar question was raised.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources