asked on

HOW DO I CREATE STORED PROCEDURE / TRIGGER IN SQLSERVER 2005

My questions is the following. I have an MS access program that allows users to enter detailed event logs. However, I want to create a trigger in MSSQL SERVER 2005 and be able to update the table T_LOG based on the event type added at T_LogEvents.

So for example. If a created the following record in T_LOGEVENTS...
-EventID: 05513546 (this is an autonumber)
-EventType: DOCUMENT
-Dn_number: EP071105001
-EventDate: 12/12/07 11:53
-LogUser: epicazo
-LogAction: NOTIFIED
-ToFrom: Paula Jones

I want to the trigger in SQL2005 to acknowledge the add in T_LOGEVENTS, and then update the record in T_LOG, lets say SELECT DN_NUMBER FROM T_Log WHERE (DN_NUMBER = N'EP071105001'), and if column DBO.T_LOGEVENTS.LogAction ="NOTIFIED", then the trigger will update the T_LOG record with the following ... dbo.T_LOG.enotify=dbo.T_LogEvents.EventDate, AND T_Log.eLastEventBy=dbo.T_LogEvents.LogUser

Below are my tables. There is a relation of one (t_log) to many (t_logevents)
<<<<PARENT>>>>TABLE: dbo.t_log
...COLUMNS...
-DN_NUMBER...(PK) nvarchar(20)
-inituser...nvarchar(15)
-initdate...datetime
-eNotify...datetime
-eLastEvent...datetime
-eLastEventBy...NVARCHAR(15)

<<<<CHILD>>>>TABLE: dbo.t_LogEvents
...COLUMNS...
-EventId...(PK) numeric(18,0)... identity increment 1
-EventType...nvarchar(20)
-Dn_number...nvarchar(20)
-EventDate...datetime
-LogUser...nvarchar(15)
-LogAction...nvarchar(25)
-ToFrom...nvarchar(30)

Obviously the syntax is not correct, but I am so green in trigger, never dealt with them, so I'll need a step by step on how to create this trigger.

THANKS

Yveau

create trigger:
http://msdn2.microsoft.com/en-us/library/ms189799.aspx

create stored procedure:
http://msdn2.microsoft.com/en-us/library/ms187926.aspx

... and a trigger is just as a stored procedure. Only thing is that you can't directly run it, the RDBMS will ... One other thing, you have two 'virtual' tables in the triggers: Inserted (in insert and update trigger) and Deleted (in update and delete trigger) to help you deal with the records before the DML hit the table ...

Hope this helps ...

ASKER CERTIFIED SOLUTION

imitchie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imitchie

Run the above code from Query Analyzer or SSMStudio

epicazo

ASKER

This is perfect THANK YOU so much. Two (well three) additional questions -- if you don't mind:
Will all my triggers have the "update tl set" string?
Why name it TL and not anything else -- what exactly is it doing here

Also, could you recommend additional resources books to learn more about triggers and stored procedures? I want to learn more of this stuff -- it's awesome!

epicazo

ASKER