Hi there, all. Hope you're all doing well.

I'm once again struggling with a trigger issue.  I've written a trigger as below, one each for update, delete and insert.  When I test in DEV by doing an update, insert or delete, I don't get any error messages.  When I go check the table created for storing information as the triggers are fired, they're empty.  Is there anything wrong with the trigger?  Any help would be greatly appreciated!

USE [JDE_CRP]
GO

/****** Object:  Trigger [CRPDTA].[F0150_PDS_Delete]    Script Date: 09/30/2008 08:40:23 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [CRPDTA].[F0150_PDS_Delete]
ON [CRPDTA].[F0150]
AFTER DELETE
AS

INSERT INTO CRPDTA.SC0150(CXCKEY, CXEDSP, CXAN8, CXSCDT, CXATIM, CXADLJ, CXARTM)
select CXCKEY = ('SC0150   ' + CONVERT(char(23), CURRENT_TIMESTAMP, 21)),
CXEDSP = '0',
CXAN8 = CRPDTA.F0150.MAAN8,
CXSCDT = CRPDTA.F0150.MAUPMJ,
CXATIM = CRPDTA.F0150.MAUPMT,
CXADLJ = '0.0',
CXARTM = ''
from DELETED d, CRPDTA.F0150

The 'before' rows can only be read from the deleted snapshot, not from the table itself.

Not quite sure what kind of results you are expecting by using FROM deleted d, CRPDTA.F0150 without JOIN or WHERE, but I would expect a cartesian product --- without JOIN or WHERE, the complete remaining contents of CRPDTA.F0150 will be repeated for each row in the deleted snapshot.

So  I presume that your test involved deleting all rows from CRPDTA.F0150 ...

I'm also not exactly sure what you are asking.  What we're trying to achieve is this:

Each time an update, insert or delete takes place, it needs to be written to the SC0150 table (the selected information).  It's just to give us an idea of when something was changed more or less.

You're showing us an AFTER DELETE trigger. So far, so good.

When an AFTER DELETE trigger fires, the rows that are being deleted can only be read from the deleted snapshot, since they no longer appear in the table itself. Note that the column list in your SELECT clause only refers to columns in the table itself - it should refer to columns from the deleted snapshot.

Any query that has a FROM table1, table2 clause without a JOIN or a WHERE that implements join-logic, will return a cartesian product - next to each row from table1, all rows from table2 will be shown. If there are 2 rows in table1, and 5 in table2, the query will return 10 rows. --- And if either table has no rows, the cartesian product is zero rows.

Thank you so much for your help!  You have clarified what has been confusing me all along!  It's now working great.

Oi vey.  They now complain that they get an error when they try to insert.  They say it makes an entry in the table that's created in the trigger, but it makes no changes in the original table, and then freezes up the system.  Does things look okay with the trigger:

You have an INSERT INTO query, so you're not creating the CRPDTA.SC0150 table. The target table must already exist, before the trigger is fired.

If you would use a SELECT column_list INTO new_table FROM existing_table query (which is not recommended, exactly because of freeze-ups etc.) - indeed that would create a new table.

You'll need to post the exact error message, better than to rely on those users.

That's just the problem.  When I run the insert in SQL Management Studio's Query Analyzer, it inserts the information in F0150 AND inserts the necessary information into SC0150.  But when the developer tests it from within the application, he gets errors.  I'm seriously confused here.

The SC0150 table DOES already exist.

Well, that is exactly why you need to see the exact error message, instead of vague descriptions from users.

Probably the first thing to check is permissions on the SC0150 table, since the developers may not have the same permissions as you.