Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

HOW DO I CREATE STORED PROCEDURE / TRIGGER IN SQLSERVER 2005

Posted on 2007-12-06
5
Medium Priority
?
244 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:epicazo
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20423072
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 ...
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20423082
create trigger trigger_T_logevents
on T_logevents
after insert
as
begin

update tl set
  enotify=inserted.EventDate,
  eLastEventBy=inserted.LogUser
from dbo.t_log tl
  inner join inserted on tl.DN_NUMBER = inserted.DN_NUMBER
where inserted.LogAction ='NOTIFIED'

end
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20423092
Run the above code from Query Analyzer or SSMStudio
0
 

Author Comment

by:epicazo
ID: 20429207
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!


0
 

Author Closing Comment

by:epicazo
ID: 31413254
:)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question