Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-11 : 07:11:26
|
| Gretchen writes "I am trying to create my first trigger that inserts a record into the reportsecurity table for employee id 2, 4, and 153 each time a report is entered into the reports table. I put the trigger on the reports table. When I put a new report into the reports table, nothing happens. What am I doing wrong?Here is my trigger:CREATE TRIGGER [ITReportAccess] ON [dbo].[Reports] for INSERT ASif not exists(select S.reportid from reportsecurity S inner join reports R onS.reportid = R.reportid)insert reportsecurity(reportid, userid)(select reports.reportid, 2 from reports)(select reports.reportid, 4 from reports)(select reports.reportid, 153 from reports)" |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-11 : 08:45:48
|
| <quote>(select S.reportid from reportsecurity S inner join reports R on S.reportid = R.reportid) </quote>This statement returns rows the exists in *both* tables, while you need to get the new rows inserted into reports (that do not exist in This is an INNER JOIN. It means that).What you should do is get the new values (reports.reportid) from inserted (a virtual table that holds all the rows inserted). Take into consideration that there can be more then 1 row inserted, and you must make sure you insert this 3 lines for each new row.Bambola. |
 |
|
|
|
|
|
Trigger to insert into a table