difficult Trigger to record an insertion

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)

difficult Trigger to record an insertion

Author

Topic

barcelo
Starting Member

20 Posts

Posted - 2013-10-14 : 12:47:00

Friends! I need to create a trigger to perform the following action:

When you insert new data in the (Employee) table, the trigger must check if there is already a row in the table (Assistance) with "Date" and "Id_Employee" of equal value that entered into the (Employee) table THEN:

1 - If not exist, create a new row in the table (Assistance) with the values ??entered in the (Employee) table:
Assistance.Date = Employee.Date AND Assistance.Id_Employee = Employee.Id_Employee, then the values of
"IN" and "OUT" from table (Employee) if "Order" is 1 the values will in "IN_AM" and "OUT_AM"
in the Assitance table, if "Order" is 2 the values will in "IN_PM" and "OUT_PM".

2 - If exist, only need to update the values ??of the fields [IN_AM, OUT_AM] or [IN_PM, OUT_PM], depending on whether order is 1 or 2

Employee <------TABLE 1
---------------------------------------------
Id_Employee--Order--IN-----OUT----Date
21866........1......07:55..12:05..4/10/2013
21866........2......12:55..17:01..4/10/2013
05214........1......08:05..12:15..4/10/2013
05214........2......13:00..17:08..4/10/2013
=============================================

Assistance <------TABLE 2
-----------------------------------------------------------
Date------Id_Employee----IN_AM----OUT_AM----IN_PM----OUT_PM
4/10/2013..21866.........07:55....12:05.....12:55....17:01
4/10/2013..05214.........08:05....12:15.....13:00....17:08
===========================================================

look better: http://pastebin.com/bd6L6a67

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 13:12:15

[code]
CREATE TRIGGER Trig_ModifyData
ON Employee
FOR INSERT
AS
BEGIN
INSERT Assistance
SELECT Date, Id_Employee,
MAX(CASE WHEN [Order] = 1 THEN IN END) AS IN_AM,
MAX(CASE WHEN [Order] = 1 THEN OUT END) AS OUT_AM,
MAX(CASE WHEN [Order] = 2 THEN IN END) AS IN_PM,
MAX(CASE WHEN [Order] = 2 THEN OUT END) AS OUT_PM
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM Assistance
WHERE Id_Employee = i.Id_Employee
AND Date = i.Date)
GROUP BY Id_Employee,Date

UPDATE a
SET IN_AM = i.IN_AM,
OUT_AM = i.OUT_AM,
IN_PM = i.IN_PM,
OUT_PM = i.OUT_PM
FROM Assistance a
INNER JOIN (
SELECT Date, Id_Employee,
MAX(CASE WHEN [Order] = 1 THEN IN END) AS IN_AM,
MAX(CASE WHEN [Order] = 1 THEN OUT END) AS OUT_AM,
MAX(CASE WHEN [Order] = 2 THEN IN END) AS IN_PM,
MAX(CASE WHEN [Order] = 2 THEN OUT END) AS OUT_PM
FROM INSERTED i
GROUP BY Id_Employee,Date
)i
ON i.Id_EMployee = a.Id_Employee
AND i.[Date] = a.[Date]
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-14 : 14:49:23

WOW! thank!
...
WHERE NOT EXISTS (SELECT 1 FROM CPT_Asistance
..
...

here is 1 or * ?

barcelo
Starting Member

20 Posts

Posted - 2013-10-14 : 16:52:27

After creating the trigger if I try to enter data in the Employees table:

INSERT INTO Employee(Id_Empleado, Date, Order, IN, OUT)
VALUES(21866,'14/10/2013 0:00:00',1,'14/10/2013 07:57:00','14/10/2013 12:10:00')

I get the following:
....in Trig_ModifyData. Line 6:
- Error converting character string to smalldatetime data type.

Column Employee Table:
---------------------------------------
- Id_Employee (vachar(7), NO NULL)
- Date (smalldatetime, NO NULL)
- IN (smalldatetime, NULL)
- OUT (smalldatetime, NULL)

Column Assitance Table:
---------------------------------------
- Id_Employee (vachar(7), NO NULL)
- Date (smalldatetime, NO NULL)
- IN_AM (smalldatetime, NULL)
- OUT_AM (smalldatetime, NULL)
- IN_PM (smalldatetime, NULL)
- OUT_PM (smalldatetime, NULL)

I try this in the trigger:
...
AND Date = CONVERT(smalldatetine, i.Date)
...

but not work.... :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-15 : 01:13:55

quote:
Originally posted by barcelo

WOW! thank!
...
WHERE NOT EXISTS (SELECT 1 FROM CPT_Asistance
..
...

here is 1 or * ?

1 is enough

you just need a boolean result to see if record exists or not

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-15 : 01:18:55

quote:
Originally posted by barcelo

After creating the trigger if I try to enter data in the Employees table:

INSERT INTO Employee(Id_Empleado, Date, Order, IN, OUT)
VALUES(21866,'14/10/2013 0:00:00',1,'14/10/2013 07:57:00','14/10/2013 12:10:00')

I get the following:
....in Trig_ModifyData. Line 6:
- Error converting character string to smalldatetime data type.

Column Employee Table:
---------------------------------------
- Id_Employee (vachar(7), NO NULL)
- Date (smalldatetime, NO NULL)
- IN (smalldatetime, NULL)
- OUT (smalldatetime, NULL)

Column Assitance Table:
---------------------------------------
- Id_Employee (vachar(7), NO NULL)
- Date (smalldatetime, NO NULL)
- IN_AM (smalldatetime, NULL)
- OUT_AM (smalldatetime, NULL)
- IN_PM (smalldatetime, NULL)
- OUT_PM (smalldatetime, NULL)

I try this in the trigger:
...
AND Date = CONVERT(smalldatetine, i.Date)
...

but not work.... :(

why are you doing the conversion? Isnt field datetime? you should make it datetime as its delaing with date values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-15 : 08:09:51

The conversion was a test, with it or without it I get the same error:
....in Trig_ModifyData. Line 6:
- Error converting character string to smalldatetime data type.

and truth, not understand why because the corresponding fields are smalldatatime data type.

If I remove the trigger, I can run the query to insert data seamlessly
INSERT INTO Employee(Id_Employee, Date, Order, IN, OUT)
VALUES(21866,'14/10/2013 0:00:00',1,'14/10/2013 07:57:00','14/10/2013 12:10:00')

The strange thing is that I can run this query directly in the table Assistance:

INSERT INTO Asistance(Id_Employee, Date, IN_AM, OUT_AM, IN_PM, OUT_PM)
VALUES(21866,'15/10/2013 0:00:00', '15/10/2013 07:57:00', '15/10/2013 12:05:00',
'15/10/2013 12:53:00', '14/10/2013 17:10:00')

Somehow the trigger is sending a string instead of smalldatatime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:30:34

can you try using iso format for passing dates and see if it works?

ie like


INSERT INTO Employee(Id_Employee, Date, Order, IN, OUT)
VALUES(21866,'20131014 00:00',1,'20131014 07:57','20131014 12:10')

also see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-16 : 08:43:31

quote:
Originally posted by visakh16

can you try using iso format for passing dates and see if it works?

ie like

INSERT INTO Employee(Id_Employee, Date, Order, IN, OUT)
VALUES(21866,'20131014 00:00',1,'20131014 07:57','20131014 12:10')

also see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Neither works well, says the same error:
Mens 295, Level 16, Status 3, Prossesing Trig_ModifyData, Línea 6
Error converting character string to smalldatetime data type.

Anyway I need that data can be sent as follows: '14/10/2013 0:00:00'

You can perform a data conversion within the trigger to fix the problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 09:54:41

quote:
Originally posted by barcelo

quote:
Originally posted by visakh16

can you try using iso format for passing dates and see if it works?

ie like

INSERT INTO Employee(Id_Employee, Date, Order, IN, OUT)
VALUES(21866,'20131014 00:00',1,'20131014 07:57','20131014 12:10')

also see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Neither works well, says the same error:
Mens 295, Level 16, Status 3, Prossesing Trig_ModifyData, Línea 6
Error converting character string to smalldatetime data type.

Anyway I need that data can be sent as follows: '14/10/2013 0:00:00'

You can perform a data conversion within the trigger to fix the problem?

why do you need to do data conversion? whats the native datatype of field from which values come?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-16 : 10:22:15

I should not do the conversion on the trigger but as I said before, the trigger will send the value of a field (smalldatatime) of the Employee table to a field too (smalldatatime) in the table Assistance
I do not understand why it is giving error

barcelo
Starting Member

20 Posts

Posted - 2013-10-16 : 11:12:56

I did the following test without the trigger to insert data from Employee table to Assistance table and run correctly:

INSERT INTO Assistance(Id_Employee, Date, IN_AM, OUT_AM)
SELECT Id_Employee, Date, IN, OUT
FROM Employee WHERE Date='16/10/2013 0:00:00' AND Order = '1'

Then why trigger not run? :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 12:45:23

What are the datatypes of IN_AM,OUT_AM etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-16 : 14:07:27

quote:
Originally posted by visakh16

What are the datatypes of IN_AM,OUT_AM etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

smalldatatime

- IN_AM (smalldatetime, NULL)
- OUT_AM (smalldatetime, NULL)
- IN_PM (smalldatetime, NULL)
- OUT_PM (smalldatetime, NULL)

barcelo
Starting Member

20 Posts

Posted - 2013-10-25 : 10:36:09

quote:

Originally posted by visakh16


CREATE TRIGGER Trig_ModifyData
ON Employee
FOR INSERT
AS
BEGIN 
INSERT Assistance
SELECT Date, Id_Employee,
MAX(CASE WHEN [Order] = 1 THEN IN END) AS IN_AM,
MAX(CASE WHEN [Order] = 1 THEN OUT END) AS OUT_AM,
MAX(CASE WHEN [Order] = 2 THEN IN END) AS IN_PM,
MAX(CASE WHEN [Order] = 2 THEN OUT END) AS OUT_PM
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM Assistance
WHERE Id_Employee = i.Id_Employee
AND Date = i.Date)
GROUP BY Id_Employee,Date

UPDATE a
SET IN_AM = i.IN_AM,
OUT_AM = i.OUT_AM,
IN_PM = i.IN_PM,
OUT_PM = i.OUT_PM
FROM Assistance a
INNER JOIN (
SELECT Date, Id_Employee,
MAX(CASE WHEN [Order] = 1 THEN IN END) AS IN_AM,
MAX(CASE WHEN [Order] = 1 THEN OUT END) AS OUT_AM,
MAX(CASE WHEN [Order] = 2 THEN IN END) AS IN_PM,
MAX(CASE WHEN [Order] = 2 THEN OUT END) AS OUT_PM
FROM INSERTED i
GROUP BY Id_Employee,Date
)i
ON i.Id_EMployee = a.Id_Employee
AND i.[Date] = a.[Date]
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Supposedly, what should I convert to smalldatetime data in the trigger to prevent the error?

Mens 295, Level 16, Status 3, Prossesing Trig_ModifyData, Línea 6
Error converting character string to smalldatetime data type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 08:03:01

Is Date smalldatetime in both the tables Employee and Assistance?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 09:48:02

quote:
Originally posted by visakh16

Is Date smalldatetime in both the tables Employee and Assistance?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

yes, yes!, Date, IN, OUT, IN_AM, OUT_PM, are smalldatetime

By the way, when I add the trigger, SQL Server automatically adds lines "**"
** set ANSI_NULLS ON
** set QUOTED_IDENTIFIER ON
** GO
** ALTER TRIGGER [dbo].[Trig_ModifyData]
** ON [dbo].[Employee]
FOR INSERT
AS
BEGIN
INSERT Assistance
SELECT Date, Id_Employee,
...
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 09:57:12

Based on your explanation you've no chance of getting this error. So its onvious that you're missing something or you may have some other code which you've not shown us so far

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 10:52:26

quote:
Originally posted by visakh16

Based on your explanation you've no chance of getting this error. So its onvious that you're missing something or you may have some other code which you've not shown us so far

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Ok,! look the real tables:

Table 1 As Employee table:

http://www.subirimagenes.net/i/131028034827199744.png

Table 1 The Columns! :
As "Id_Employee" = Id_Empleado
As "Date" = Fecha
AS "Order" = OrdenTurno
As "IN" = MarcaEntrada
As "OUT" = MarcaSalida

Table 2 As Assitance table:

http://www.subirimagenes.net/i/131028033956990112.png

Table 2 the Columns:
As "Id_Employee" = Id_Empleado
As "Date" = Fecha

As "IN_AM" = Entada_AM
As "OUT_AM" = Salida_AM

As "IN_PM" = Entada_PM
As "OUT_PM" = Salida_PM

THEN the trigger is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trig_OrganizarFirmas]
ON [dbo].[CPT_TarjetaAsistencia]
FOR INSERT
AS
BEGIN 
INSERT CPT_AsistenciaEmpleados
SELECT Fecha, Id_Empleado,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaEntrada END) AS EntradaAM,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaSalida  END) AS SalidaAM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaEntrada END) AS EntradaPM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaSalida  END) AS SalidaPM
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM CPT_AsistenciaEmpleados
WHERE Id_Empleado = i.Id_Empleado
AND Fecha = i.Fecha)
GROUP BY Id_Empleado,Fecha

UPDATE a
SET EntradaAM = i.EntradaAM,
SalidaAM = i.SalidaAM,
EntradaPM = i.EntradaPM,
SalidaPM = i.SalidaPM
FROM CPT_AsistenciaEmpleados a
INNER JOIN (
SELECT Fecha, Id_Empleado,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaEntrada END) AS EntradaAM,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaSalida END) AS SalidaAM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaEntrada END) AS EntradaPM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaSalida END) AS SalidaPM
FROM INSERTED i
GROUP BY Id_Empleado,Fecha
)i
ON i.Id_Empleado = a.Id_Empleado
AND i.[Fecha] = a.[Fecha]
END

See if you see because it gives me the error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 13:19:58

i think i got the issue. Reason is your order in SELECT is different from column order in INSERT
Try this and see


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trig_OrganizarFirmas]
ON [dbo].[CPT_TarjetaAsistencia]
FOR INSERT
AS
BEGIN 
INSERT CPT_AsistenciaEmpleados
SELECT Id_Empleado,Fecha, 
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaEntrada END) AS EntradaAM,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaSalida  END) AS SalidaAM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaEntrada END) AS EntradaPM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaSalida  END) AS SalidaPM
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM CPT_AsistenciaEmpleados
WHERE Id_Empleado = i.Id_Empleado
AND Fecha = i.Fecha)
GROUP BY Id_Empleado,Fecha

UPDATE a
SET EntradaAM = i.EntradaAM,
SalidaAM = i.SalidaAM,
EntradaPM = i.EntradaPM,
SalidaPM = i.SalidaPM
FROM CPT_AsistenciaEmpleados a
INNER JOIN (
SELECT Fecha, Id_Empleado,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaEntrada END) AS EntradaAM,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaSalida END) AS SalidaAM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaEntrada END) AS EntradaPM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaSalida END) AS SalidaPM
FROM INSERTED i
GROUP BY Id_Empleado,Fecha
)i
ON i.Id_Empleado = a.Id_Empleado
AND i.[Fecha] = a.[Fecha]
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

barcelo
Starting Member

20 Posts

Posted - 2013-10-28 : 14:15:23

quote:

Originally posted by visakh16

i think i got the issue. Reason is your order in SELECT is different from column order in INSERT
Try this and see


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trig_OrganizarFirmas]
ON [dbo].[CPT_TarjetaAsistencia]
FOR INSERT
AS
BEGIN 
INSERT CPT_AsistenciaEmpleados
SELECT Id_Empleado,Fecha, 
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaEntrada END) AS EntradaAM,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaSalida  END) AS SalidaAM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaEntrada END) AS EntradaPM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaSalida  END) AS SalidaPM
FROM INSERTED i
WHERE NOT EXISTS (SELECT 1 FROM CPT_AsistenciaEmpleados
WHERE Id_Empleado = i.Id_Empleado
AND Fecha = i.Fecha)
GROUP BY Id_Empleado,Fecha

UPDATE a
SET EntradaAM = i.EntradaAM,
SalidaAM = i.SalidaAM,
EntradaPM = i.EntradaPM,
SalidaPM = i.SalidaPM
FROM CPT_AsistenciaEmpleados a
INNER JOIN (
SELECT Fecha, Id_Empleado,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaEntrada END) AS EntradaAM,
MAX(CASE WHEN [OrdenTurno] = 1 THEN MarcaSalida END) AS SalidaAM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaEntrada END) AS EntradaPM,
MAX(CASE WHEN [OrdenTurno] = 2 THEN MarcaSalida END) AS SalidaPM
FROM INSERTED i
GROUP BY Id_Empleado,Fecha
)i
ON i.Id_Empleado = a.Id_Empleado
AND i.[Fecha] = a.[Fecha]
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Well, no longer fails when a row is inserted, the trigger sends the data to the other table :) .

But I see that does not work when you modify the row, ie when the row is updated, the trigger does nothing

I had changued this:


..
UPDATE a
....
..
SELECT Fecha, Id_Empleado,
...

for this


..
UPDATE a
....
..
SELECT Id_Empleado, Fecha,
...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources