0

I have a trigger doing an INSERT_AFTER to a different table. The destination table is a staging table for data to be sent to another company. In the database on our side, the date is a smalldatetime. The other comp;any needs this split to a Date field and a Time field. Neither make it to the staging table. I have tried several different things including CAST and CONVERT with no success.

The pertinent SQL is below:

    CAST(inserted.CallInDate AS DATE)   AS ClientCallinDate,
    CAST(inserted.CallInDate AS TIME)   AS ClientCallinTime,
    --CONVERT(DATE,inserted.CallInDate) AS ClientCallinDate,
    --CONVERT(TIME,inserted.CallInDate) AS ClientCallinTime,

This trigger is following another INSERT_AFTER trigger doing the same thing to other tables. The first trigger is fired as a "First" and the trigger with the problem is fired as a last.

`EXEC sp_settriggerorder @triggername=N'[dbo].[TR_FirstTable_to_Client_I]', @order=N'First', @stmttype=N'INSERT'`

The second trigger also has another field failing that is created by the first trigger as a confirmation of receipt from the other company. II do not think these are related, but seeing as I have not figured either out, I cannot be sure.

`EXEC sp_settriggerorder @triggername=N'[dbo].[TR_FirstTable_to_Client_II]', @order=N'Last', @stmttype=N'INSERT'`

What I need is knowledge of what could be failing or what in SQL I need to change. I have dropped the trigger and recreated it, but that was of no help, and actually gave me an error I have yet to solve.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Jim Snyder
  • 99
  • 2
  • 9
  • Which dbms are you using? (This is a product specific question.) – jarlh Nov 20 '19 at 20:02
  • My apologies! Messed up on basic posting. Microsoft SQL Server 2014. – Jim Snyder Nov 20 '19 at 20:23
  • do you really need a trigger to store a hard copy of data you could extract with a query? – KeithL Nov 20 '19 at 20:45
  • 1
    Without code and a clear definition of what "with no success" means, it is impossible to make useful suggestions. – SMor Nov 20 '19 at 21:49
  • What are the actual error messages you are getting? What statements/lines of code are throwing these error messages? (You can work this out by adding `PRINT` messages in your triggers). What do you mean by "The second trigger also has another field failing that is created by the first trigger"? How does your trigger create fields? Keep in mind that we have no knowledge of your specific setup, other than what you tell us. – Alex Nov 21 '19 at 00:14

1 Answers1

0

If your code above is part of an INSERT statement, it might just be failing because of the aliasing: AS ClientCallinDate. As long as your statement is constructed correctly, you do not need to alias the columns in the SELECT statement.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28