Cant Seem to Figure out a Trigger...

Hey everyone,

First post (of hopefully many) to come...

I am currently writing a little Football Pool Application and I need to create a trigger (I think anyways) but I cannot for the life of me figure it out.

I won't bore people with the entire schema. What is needed to know is that I have a "Game" table which stores the ID's of two teams playing, and their respective scores (0 until after the game ends), I have a UserTable which stores a "card id" (basically a set of picks) and then I have UserPicksTable which basically just links to the UserCards as well as has a "WinningTeam" field, which will hold the ID of the team that the user in question has picked to win.

So every Tuesday morning I will enter the game scores into the "Game" table and I want this trigger to give a point to all the users that picked that game correctly...

If anyone could give me a hand, or a boot, in the right direction it would be a huge help. I can't even figure out where to start...

thanks,

--dbWhich database is it? Triggers sound like Oracle; if so, perhaps you could consider using a job scheduler (DBMS_JOB or DBMS_SCHEDULER) which would, depending on the results, send mail to users (or even all of them).

Cant reference logical table within cursor

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.SQL = 'select ' + @.column_name + ' into ##DeletedData from deleted'
exec sp_executesql @.SQL
set @.SQL = 'select ' + @.column_name + ' into ##InsertedData from inserted'
exec sp_executesql @.SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.

TIAThe error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.|||That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

Thanks again|||Maybe you can tell us what you are trying to do...I can't figure it out...

AND a cursor in a trigger is never a good idea....|||Brett -

I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

Thanks|||Why bother...just update the whole row...what difference does it make...

What's the DDL of the table look like?

What do you mean by dynamic?|||DDL ("master" audit table):
/****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
CREATE TABLE [contacts_history] (
[contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
[contact_id] [int] NULL ,
[who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
(
[contact_history_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

DDL (data capture table):
/****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
CREATE TABLE [contacts_history_values] (
[contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
[contact_history_id] [int] NULL ,
[field_id] [int] NULL ,
[old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
(
[contacts_history_value_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

Thanks|||holy crap...

You basically have "vertical" rows....

Is this a third party product, did you build this or did you inherit this

What does the concat table DDL look like?

And what do you mean the "fields" (you mean columns right) can change on the fly.

Do you mean the data or the table structure?

What does "if any of the column_name values have changed" mean?

I'm sorry...I just can't see it yet...|||Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

I mean the table structure.

What does "if any of the column_name values have changed" mean?
--> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

Hope this makes sense.|||ok...I understand...not that I want to.....

Is the id 1 per "table"

Are you making up and configuring "tables" for end users through a web application?

I'd be very interested in the reason that your doing this...

As for your solution...I gotta ponder this one.....

Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

It doesn't work that way.

You column name is essentially a key in this process...actually a composite key along with contactId

So actually trying to udate the key...which to me is a bad thing...

You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

That should be rather straight forward in a trigger...|||Sorry, but I don't follow you.

Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
This allows the app to keep track of the column that was modified.

I think I'm more confused now... =\|||Ok good...

I think I got confused...

You want to audit the change correct?

Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

What happens for a new "field" or a field that has been "deleted"

You still haven't told me who built this...|||I think I found the right design but I'm having issues executing it.

DECLARE check_contact_fields CURSOR LOCAL
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if (select '+@.column_name+' from inserted) <> (select '+@.column_name+' from deleted)
print @.column_name + ' boo'
else if (select '+@.column_name+' from inserted) = (select '+@.column_name+' from deleted)
print 'identical values'
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

This always returns "identical values" even though some of the data has changed.

Any ideas?|||What table is the Trigger on?

I don't think you want or need the cursor|||I Think you want this...can't test without the other tables DDL

CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
AS
BEGIN
INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
FROM inserted i INNER JOIN deleted o
WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
END

Can't receive Message from Queue (Async Trigger)

Hi Folks,

I've found a pretty good code example on http://www.dotnetfun.com for a Asynchronous Trigger.

I've parsed through the Code, to understand how to wirte my own Async Trigger with a Service Broker, but the Code isn't working! It seems that the stored procedure don't receive the messages in the queue, but the queue get's filled.

MessageType

CREATE MESSAGE TYPE myMsgXML
VALIDATION = WELL_FORMED_XML;

Contract

CREATE CONTRACT myContractANY
(myMsgXML SENT BY ANY)

Queue

CREATE QUEUE myQueue
WITH STATUS = ON,
RETENTION = ON,
ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = sp_myServiceProgram,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF
)

Service

CREATE SERVICE myService ON QUEUE myQueue (myContractANY)

Procedure (greped from http://www.dotnetfun.com/)

CREATE PROC sp_myServiceProgram
AS
-- This procedure will get triggered automatically
-- when a message arrives at the
-- Let's retrieve any messages sent to us here:
DECLARE @.XML XML,
@.MessageBody VARBINARY(MAX),
@.MessageTypeName SYSNAME,
@.ID INT,
@.COL2 VARCHAR(MAX);
DECLARE @.Queue TABLE (
MessageBody VARBINARY(MAX),
MessageTypeName SYSNAME);
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE message_body, message_type_name
FROM myQueue INTO @.Queue
), TIMEOUT 5000;
-- If no messages exist, then break out of the loop:
IF NOT EXISTS(SELECT * FROM @.Queue) BREAK;
DECLARE c_Test CURSOR FAST_FORWARD
FOR SELECT * FROM @.Queue;
OPEN c_Test;
FETCH NEXT FROM c_Test
INTO @.MessageBody, @.MessageTypeName;
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Let's only deal with messages of Message Type
-- myMsgXML:
IF @.MessageTypeName = 'myMsgXML'
BEGIN
SET @.XML = CAST(@.MessageBody AS XML);
-- Now let's save the XML records into the
-- historical table:
INSERT INTO tblDotNetFunTriggerTestHistory
SELECT tbl.rows.value('@.ID', 'INT') AS ID,
tbl.rows.value('@.COL2', 'VARCHAR(MAX)') AS COL2,
GETDATE() AS UPDATED
FROM @.XML.nodes('/inserted') tbl(rows);
END
FETCH NEXT FROM c_Test
INTO @.MessageBody, @.MessageTypeName;
END
CLOSE c_Test;
DEALLOCATE c_Test;
-- Purge the temporary in-proc table:
DELETE FROM @.Queue;
END

Send Message in a Update Trigger

SELECT @.XML = (SELECT * FROM inserted FOR XML AUTO);
-- Send the XML records to the Service Broker queue:
DECLARE @.DialogHandle UNIQUEIDENTIFIER,
@.ConversationID UNIQUEIDENTIFIER;
/*
The target Service Broker service is the same
service as the initiating service; however, you
can set up this type of trigger to send messages
to a remote server or another database.
*/
BEGIN DIALOG CONVERSATION @.DialogHandle
FROM SERVICE myService
TO SERVICE 'myService'
ON CONTRACT myContractANY;
SEND ON CONVERSATION @.DialogHandle
MESSAGE TYPE myMsgXML
(@.XML);
-- Let's detect an error state for this dialog
-- and rollback the entire transaction if one is
-- detected:
IF EXISTS(SELECT * FROM sys.conversation_endpoints
WHERE conversation_handle = @.DialogHandle
AND state = 'ER')
RAISERROR('Dialog in error state.', 18, 127);
ELSE
BEGIN

--I want to list the queue after the trigger so I disabled

--END CONVERSATION @.DialogHandle;
COMMIT TRAN;
END

The Problem is, that the Procedure doesn't even get started! So I tried to receive the Queues manually

WAITFOR (
RECEIVE message_body, message_type_name
FROM myQueue INTO @.Queue
), TIMEOUT 5000;

and I run always into the timeout and get nothing back. A Select * FROM myQueue gives me some results back. Why I can't recevie?

Would be grateful for help, or at least a good tutorial, I haven't found one yet....

thx and greez

Karsten

Since you have retention enabled on the queue, when you do select * from myQueue, you are probably seeing the sent messages instead of the delivered messages. Look at the 'status' column on the queue in the above query.

If the messages are not being delivered, it is likely that they are pending in the transmission queue:
select * from sys.transmission_queue

The transmission status column of that view should tell you why the messages could not be delivered. If it is related to security, you probably need to create a database master key in the database. You can do that as follows:
create master key encryption by password='YourP@.$$word';