Applying business rules to the database

Hi Experts,
My system stores events into a large table. I would like to apply some
rules on that table and possibly take action if the rules were satisfied.
For example if a certain event arrives 2 times in 60 seconds then perform an
action. What is the best way to apply rules to data in the database? If it
cannot be done with the database itself a recommendation of a third party
tool will be great. However please not that I cannot use any GUI. I need
to declare the rules and get the result of the rule into my application
which can be in C++ or C#
Thanks for all the help,
AviHave you looked at streaming databases? Google up BitStream.|||You could use triggers in SQL but if you need to invoke external code
against the database then it's better to put this in a middle-tier
application. In your middle-tier code, poll the table regularly for the
changes based on a date-time stamp column.
David Portas
SQL Server MVP
--

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>

APP_NAME() empty when deleting

I was trying to set up auditing triggers on a table and was using APP_NAME()
to get the name of the application that performed the action. APP_NAME()
always returns "SQL Server Management Studio - Query" when I'm testing in
that environment and executing INSERTs or UPDATEs. But for some unknown
reason, it returns blank when executing DELETE.
I haven't explicitly set the application name but I was expecting it to
still show "SQL Server Management Studio" here, especially since DELETEs are
as worthy of auditing as anything else. Is this by design? Any idea why?
Jon>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
I can't seem to repro using the script below. What SQL Server version and
service pack are you using?
CREATE TABLE dbo.MyTable
(
MyColumn int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
)
GO
CREATE TABLE dbo.MyTableAudit
(
AuditID int IDENTITY NOT NULL
CONSTRAINT PK_MyTableAudit PRIMARY KEY,
MyColumn int NOT NULL,
TableAction varchar(20) NOT NULL,
ActionDateTime datetime NOT NULL,
Application nvarchar(128) NULL,
UserName nvarchar(128) NULL,
)
GO
CREATE TRIGGER TR_MyTable_Insert
ON dbo.MyTable FOR INSERT
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Insert', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Update
ON dbo.MyTable FOR UPDATE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Update (before)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
UNION ALL
SELECT MyColumn, 'Update (after)', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM inserted
GO
CREATE TRIGGER TR_MyTable_Delete
ON dbo.MyTable FOR DELETE
AS
INSERT INTO dbo.MyTableAudit
SELECT MyColumn, 'Delete', GETDATE(), APP_NAME(), SUSER_SNAME()
FROM deleted
GO
INSERT INTO dbo.MyTable VALUES(1)
GO
UPDATE dbo.MyTable
SET MyColumn = 2
GO
DELETE FROM dbo.MyTable
GO
SELECT * FROM dbo.MyTableAudit
GO
DROP TABLE dbo.MyTable, dbo.MyTableAudit
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:OhB8h2cjHHA.3120@.TK2MSFTNGP05.phx.gbl...
>I was trying to set up auditing triggers on a table and was using
>APP_NAME() to get the name of the application that performed the action.
>APP_NAME() always returns "SQL Server Management Studio - Query" when I'm
>testing in that environment and executing INSERTs or UPDATEs. But for some
>unknown reason, it returns blank when executing DELETE.
> I haven't explicitly set the application name but I was expecting it to
> still show "SQL Server Management Studio" here, especially since DELETEs
> are as worthy of auditing as anything else. Is this by design? Any idea
> why?
> Jon
>